BRIDGE which contains the following fields: HIRE_CODE PREV_CODE START_DATE (date field) END_DATE (date field)
In the BRIDGE table, the period for which the PREV_CODE is valid for that hire (HIRE_CODE) is indicated.
I would like to create a new table where for each hire (PREV_CODE), the valid PREV_CODE is indicated for each month within the period indicated from HIRE_DATE to END_DATE_F.
You can use the IntervalMatch function to match the dates with the HIRE and BRIDGE tables. Here's how you can do it:
Temp: Load HIRE_CODE, date(monthstart(HIRE_DATE) + IterNo() - 1) as MonthYear Resident HIRE While monthstart(HIRE_DATE) + IterNo() - 1 <= monthstart(END_DATE_F);
Left Join (Temp) Load HIRE_CODE, PREV_CODE, monthstart(START_DATE) as StartMonthYear, monthstart(END_DATE) as EndMonthYear Resident BRIDGE;
NewTable: Load HIRE_CODE, PREV_CODE, Year(MonthYear) as YEAR, Month(MonthYear) as MONTH Resident Temp Where MonthYear >= StartMonthYear and MonthYear <= EndMonthYear;
Drop Table Temp;
In this script, a temporary table Temp is created that generates a record for each month between HIRE_DATE and END_DATE_F for each HIRE_CODE. Then, the BRIDGE table is joined with Temp based on HIRE_CODE and the month-year falling within the START_DATE and END_DATE range. Finally, the NewTable is created with the desired fields and the temporary table is dropped.
I hope this helps! Let me know if you have any other questions.
You can use the IntervalMatch function to match the dates with the HIRE and BRIDGE tables. Here's how you can do it:
Temp: Load HIRE_CODE, date(monthstart(HIRE_DATE) + IterNo() - 1) as MonthYear Resident HIRE While monthstart(HIRE_DATE) + IterNo() - 1 <= monthstart(END_DATE_F);
Left Join (Temp) Load HIRE_CODE, PREV_CODE, monthstart(START_DATE) as StartMonthYear, monthstart(END_DATE) as EndMonthYear Resident BRIDGE;
NewTable: Load HIRE_CODE, PREV_CODE, Year(MonthYear) as YEAR, Month(MonthYear) as MONTH Resident Temp Where MonthYear >= StartMonthYear and MonthYear <= EndMonthYear;
Drop Table Temp;
In this script, a temporary table Temp is created that generates a record for each month between HIRE_DATE and END_DATE_F for each HIRE_CODE. Then, the BRIDGE table is joined with Temp based on HIRE_CODE and the month-year falling within the START_DATE and END_DATE range. Finally, the NewTable is created with the desired fields and the temporary table is dropped.
I hope this helps! Let me know if you have any other questions.
