My table from PeopleSoft (PS_JOB) has ID Numbers (my example, there is only one emplid 12345).
The IDs can have multiple records (my example, the ID has 3 records total: 0, 1, 2).
Each row has an EFFDT (effective start date), END_DATE (effective end date) and an effective sequence.
The empl_Rcds can be active or terminated.
I need help identifying certain REH rows that need the action_Reason fixed.
If an ID was fully terminated (i.e. all records that exist have empl_Status T), then the next rehire action_reason should be REH. This is displayed correctly in rows 4 and 10 (REH-REH).
If an ID has any active record, then the next rehire should be ADL. This is displayed correctly in rows 6 and 14 (REH-ADL).
Rows 11 and 16 are both wrong and should be ADL since there was always at least one active record before the rehire. These are the two rows I’d like to select in a SQL.
I can’t figure out how to isolate the problem rows. I was attempting using the EFFDT and END_DATE but I’m not anywhere near figuring this out.
Maybe lag or lead would help?
Identifying rows within multiple date ranges is something I’ve never been able to select…
| EMPLID | EMPL_RCD | EFFDT | END_DATE | EFFSEQ | ACTION | ACTION_REASON | EMPL_STATUS |
|---|---|---|---|---|---|---|---|
| 12345 | 0 | 6/14/21 | 3/1/22 | 0 | HIR | INH | A |
| 12345 | 0 | 3/01/22 | 9/1/22 | 0 | PAY | SPI | A |
| 12345 | 0 | 9/01/22 | 11/1/22 | 0 | TER | CTG | T |
| 12345 | 0 | 11/1/22 | 2/1/23 | 0 | REH | REH | A |
| 12345 | 0 | 2/1/23 | 4/1/23 | 0 | PAY | CWP | A |
| 12345 | 1 | 3/1/23 | 5/1/23 | 0 | REH | ADL | A |
| 12345 | 0 | 4/1/23 | 8/16/23 | 0 | TER | CTG | T |
| 12345 | 1 | 5/1/23 | 7/1/23 | 0 | PAY | CWP | A |
| 12345 | 1 | 7/1/23 | 10/1/23 | 0 | TER | CTG | T |
| 12345 | 0 | 8/16/23 | 12/1/23 | 0 | REH | REH | A |
| 12345 | 1 | 10/1/23 | 10/16/23 | 0 | REH | REH | A |
| 12345 | 1 | 10/16/23 | 12/16/23 | 0 | PAY | CWP | A |
| 12345 | 0 | 12/1/23 | 3/1/24 | 0 | TER | CTG | T |
| 12345 | 2 | 12/1/23 | 4/16/24 | 0 | REH | ADL | A |
| 12345 | 1 | 12/16/23 | 1/1/50 | 0 | TER | CTG | T |
| 12345 | 0 | 3/1/24 | 6/16/24 | 0 | REH | REH | A** |
| 12345 | 2 | 4/16/24 | 1/1/50 | 0 | TER | CTG | T |
| 12345 | 0 | 6/14/21 | 1/1/50 | 0 | TER | CTG | T |
All the selects I’ve been trying haven’t worked 🙁 They’re not even worth sharing.
Descagnia is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.