Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
after several attempts, i need your help.
I have 2 tables composed as follows:
HIRE which contains the following fields:
HIRE_CODE HIRE_DATE (date field)
END_DATE_F (date field)
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.
The table I expect might be:
HIRE_CODE
PREV_CODE
YEAR
MONTH
Thanks for your support
Mauro
Hi,
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.
Hi,
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.
Great!
Thank you
Mauro