Getting a Headcount

Question

I'm trying to create a query that returns a list of active employees on a specific date so I can grab a daily headcount for each division/position.  Our data stores just the changes so it would look something like this:

ID #   Position Title                                              Termination Desription    Transaction Date   Status Change Description

108    ADMINISTRATIVE SPECIALI                                                                 4/1/2019               Temp Assignment

111    COMMANDER                                                                                      2/11/2019             GRADE CHANGE

114    **WAIT/NON-EXEMPT RETIREE                 RETIRED                            1/31/2019            TERMINATED EMPLOYEE

111    SHIFT SUPERVISOR                                                                               4/14/2019            Transfer

Ideally, I'd like to pick a date, and I would get a return of all employees with the position they would have on that date, but only return them if they are a current employee on that date.  So with the above data, ID# 114 would not return with any date after 1/31/2019 as they are retired.  Also, if I asked for date 4/1/2019, I would return ID# 111 under the "COMMANDER" position, and not see the return of "SHIFT SUPERVISOR".  When I ask for date 5/1/2019, I would return ID#111 under the "SHIFT SUPERVISOR" position but NOT under the "COMMANDER" position.

I know this is a little complicated, which is why I'm asking for some help with it.  This database has some 100k rows or more, and it gets less standardized as it gets older.  Thank you for any help you can give me!

Answer 1

PS:  You could probably shorten that to:

PARAMETERS [Enter operative date:] DATETIME;

SELECT *

FROM [YourTable] AS T1

WHERE [Status Change Description] <> "Terminated Employee"

AND [Transaction Date] =

    (SELECT MAX([Transaction Date])

     FROM [YourTable] AS T2

     WHERE T2.[ID #] = T1.[ID #]

     AND [Transaction Date] <= [Enter operative date:]);

Which should also cater for any employee who has left and then been reappointed, as happened in my department, where one officer left to join another authority, and then came back to a more senior position with us.

Answer 2

But i only want the most recent return.  If there are a dozen statis histories for the same employee, i only want the most recent position before the given date. So if i use 1/1/2019, i want to return the position listed in the most recent status change that was made before 1/1/2019.

Thank you so much for the assistance on this!!

Answer 3

Perhaps a query like this:

SELECT * FROM YourTable WHERE [Transaction Date]>=#05/20/2019# (or whatever date you want)

If you want to exclude records with a value in the Termination Description field:

SELECT * FROM YourTable WHERE [Transaction Date]>=#05/20/2019# AND [Termination Description] IS NULL

Answer 4

Correct, prior to that date, i would use the next most recent transaction for that person.  If they don't have a prior date, they wouldn't be employed so i wouldn't want them returned for the query.

Answer 5

So "Transaction Date" is the date of the "Position Title"? In other words, on 4/1/2019, ID 108 held a Position Title of "Administrative Speciali"? But PRIOR TO that date, that ID was not associated with the specific Position Title?