Write a query to list any members who haven't migrated to the new forums

[wilson29thid]

Please construct a query to check for any members with an active assignment that have forum_member_id set to 0 and provide it to @Wheatles29 so we can have companies get on their remaining folks.

It would be nice to have a separate list of cadets in active TPs to provide to FSgt. Cyr

Please post the query here so you and I can run it repeatedly until there are no results left.

If this were the day after the migration I'd call it medium-priority but considering drills are back in effect, it's pretty critical to have this list.

[swomma]

@wilson29thid Something like that should do:

SELECT 
  (
    SELECT abbr
    FROM ranks
    WHERE m.rank_id = id
  ) AS rank, 
  m.last_name, 
  m.status, 
  m.id, 
  u.abbr
FROM members m
LEFT JOIN assignments a ON a.member_id = m.id
LEFT JOIN units u ON a.unit_id = u.id
WHERE m.id IN 
  (
    SELECT DISTINCT member_ID
    FROM assignments
    WHERE end_date IS NULL
  )
  AND m.forum_member_id =0
  AND a.end_date IS NULL 
  AND u.class <>  'Staff'
ORDER BY abbr, last_name

[Wheatles29]

List broken down and sent to HQ's and Chief of LH

[wilson29thid]

Can this be added to source control at personnel-api/assets/scripts?

[swomma]

Added script to assests. It can be run at http://api.29th.org/assets/scripts/missinglist.php

[wilson29thid]

@Wheatles29 see above for the URL. Send the dogs after them!