Match/Unmatch Query need

[kwooten]

I am trying to build some turnover reporting and am having some issues. The way our system work is if an employee is put on Severance then their status is changed to 313. Once they come off Severance they are then assigned a turnover code which is >399.



What I need is a query that will give me the employees who have been assigned a 313 and their "Severance Date". If the severed employee has gone through the full process then I only want the severance date and not the "termdate" which is the date they were given the code >399. If the employee has never been on severance then i need that employee with their termdate also. I am mainly having trouble with getting only the Severance date if the employee has been severed and also given a term code.

I have attached a small db with the queries for severance and also terminated.

I would appreciate any help offered.

[June7]

I did a query that joins the two queries to the table (jointype 'Include all records from STATHIST_MASTER ...')

The table has 56,689 records.

The query has 57,196 records. This means some employees have multiple severance and terminated records. Should this be possible? Do you have employees repeatedly coming and going?

[kwooten]

Im not sure there are that many but yes that does happen. I know EID: 421174238 is one of them. It is not uncommon for a position to get eliminated (severance) and we are unable to place the employee into another role. When something comes open we may rehire that employee and sometime later the employee may decide he or she doesnt want to be in that position so they start job hunting and leave.

[June7]

Okay, then do you want only the most recent record for each employee or is that irrelevant and you just want to count bodies that come and go regardless if they are returnees?

I am also having a hard time grasping the criteria involved.

[kwooten]

Our database tables are hard to follow. The way data is entered makes it very difficult for me and I spend more time normalizing the data then it takes to build the report. I have a hard time grasping it too..

If an employee has ever been on severance (313) then i want that date. If the employee has never had a 313 then I want that date associated with their >399 status.. However, if like in the case of EID: 421174238, if they have been on severance and have returned then termed without going on severance then i want both the severance date (7/1/2010) and the term date (8/9/2012).. I have attached an example of 421174238 so you can see the progression in status.

[June7]

Do you want only the latest severence and the latest termed for each EID?