I have a table that contains effective dated transaction records for each employee. I want to create a results set that reflects the field values for each employee as of the end of each year from 2018 thru 2022. The base SQL looks like this:
/*
END OF PERIOD DATES
2021 - 01/02/2022
2020 - 01/03/2021
2019 - 12/29/2019
2018 - 12/30/2018
*/
DECLARE @PeriodYear int = 2020;
DECLARE @PeriodEnd date = '01/03/2021';
SELECT
/* Change End of Period Date Here ********************************* */
@PeriodYear AS 'Period',
/* Change End of Period Date Here ********************************* */
@PeriodEnd AS 'Period End Date',
A.EMPLID AS 'ID',
B.NAME AS 'Name',
A.EMPL_STATUS AS 'Status',
A.DEPTID AS 'Dept'
/* Change End of Period Date Here ****************************************************** */
YEAR(@PeriodEnd) - YEAR(B.BIRTHDATE) -
CASE
WHEN MONTH(@PeriodEnd) * 100 + DAY(@PeriodEnd)
>= MONTH(B.BIRTHDATE) * 100 + DAY(B.BIRTHDATE) THEN 0
ELSE 1
END AS 'Age at Period End'
FROM EMPL_TRANS A, PERSONAL_DATA B
WHERE
/* Change End of Period Date Here ************************************* */
A.LAST_HIRE_DT <= @PeriodEnd
/* Change End of Period Date Here ************************************* */
AND ( A.TERMINATION_DT > @PeriodEnd
OR A.TERMINATION_DT IS NULL)
AND A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM EMPL_TRANS A_ED
WHERE A.EMPLID = A_ED.EMPLID
/* Change End of Period Date Here ******************************************** */
AND A_ED.EFFDT <= @PeriodEnd )
AND A.EMPLID = B.EMPLID
;
Here are the input tables:
[EMPL_TRANS table][1]
| EMPLID | EFFDT | EMPL_STATUS | ACTION | DEPTID | LAST_HIRE_DT | TERMINATION_DT |
|--------|----------|-------------|--------|--------|--------------|----------------|
| 244848 | 11/11/20 | Inactive | TERM | HO1164 | 2/2/17 | 11/11/20 |
| 244848 | 3/3/18 | Active | XFER | HO1164 | 2/2/17 | |
| 244848 | 2/2/17 | Active | HIRE | I1120 | 2/2/17 | |
| 291302 | 4/3/02 | Active | HIRE | I1124 | 4/3/02 | |
| 251258 | 4/2/21 | Inactive | TERM | S1214 | 5/3/01 | 4/2/21 |
| 251258 | 5/2/20 | Active | XFER | S1214 | 5/3/01 | |
| 251258 | 5/3/01 | Active | HIRE | DU1245 | 5/3/01 | |
| 283485 | 3/3/19 | Inactive | TERM | DU1318 | 7/1/12 | 3/3/19 |
| 283485 | 7/1/12 | Active | HIRE | DU1318 | 7/1/12 | |
| 302912 | 8/5/05 | Inactive | TERM | DU1317 | 6/1/03 | 8/5/05 |
| 302912 | 6/1/03 | Active | HIRE | DU1317 | 6/1/03 | |
[PERSONAL_DATA table][1]
| EMPLID | Name | Birthdate |
|--------|--------------------------|-----------|
| 244848 | Larone,Adam | 1/1/80 |
| 291302 | Ireson,Jonathan P | 1/2/80 |
| 251258 | Benjelloun,Mohammed Simo | 1/3/80 |
| 302912 | Larocque,Joseph D | 1/4/80 |
| 283485 | Schier,Matthew | 1/5/80 |
This is the result set I'm looking for
[Result set table][1]
| Period | Period End Date | "ID" | Name | Status | Dept | Age at Period End |
|--------|-----------------|--------|--------------------------|----------|--------|-------------------|
| 2018 | 12/30/18 | 244848 | Larone,Adam | Active | I1120 | 34 |
| 2018 | 12/30/18 | 251258 | Benjelloun,Mohammed Simo | Active | DU1245 | 38 |
| 2018 | 12/30/18 | 283485 | Schier,Matthew | Active | DU1318 | 31 |
| 2018 | 12/30/18 | 291302 | Ireson,Jonathan P | Active | I1124 | 40 |
| 2018 | 12/30/18 | 302912 | Larocque,Joseph D | Inactive | DU1317 | 50 |
| 2019 | 12/29/19 | 244848 | Larone,Adam | Active | HO1164 | 35 |
| 2019 | 12/29/19 | 251258 | Benjelloun,Mohammed Simo | Active | DU1245 | 39 |
| 2019 | 12/29/19 | 283485 | Schier,Matthew | Inactive | DU1318 | 32 |
| 2019 | 12/29/19 | 291302 | Ireson,Jonathan P | Active | I1124 | 41 |
| 2019 | 12/29/19 | 302912 | Larocque,Joseph D | Inactive | DU1317 | 51 |
| 2020 | 1/3/21 | 244848 | Larone,Adam | Inactive | HO1164 | 36 |
| 2020 | 1/3/21 | 251258 | Benjelloun,Mohammed Simo | Active | S1214 | 40 |
| 2020 | 1/3/21 | 283485 | Schier,Matthew | Inactive | DU1318 | 33 |
| 2020 | 1/3/21 | 291302 | Ireson,Jonathan P | Active | I1124 | 42 |
| 2020 | 1/3/21 | 302912 | Larocque,Joseph D | Inactive | DU1317 | 52 |
| 2021 | 1/2/22 | 244848 | Larone,Adam | Inactive | HO1164 | 37 |
| 2021 | 1/2/22 | 251258 | Benjelloun,Mohammed Simo | Inactive | S1214 | 41 |
| 2021 | 1/2/22 | 283485 | Schier,Matthew | Inactive | DU1318 | 34 |
| 2021 | 1/2/22 | 291302 | Ireson,Jonathan P | Active | I1124 | 43 |
| 2021 | 1/2/22 | 302912 | Larocque,Joseph D | Inactive | DU1317 | 53 |
I want to have the SQL run for each of the date combinations (i.e. change the two parameters accordingly) but have it all combined into one result set. Is that possible?