Create a new table

[mauvasco62]

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

[Sayed_Mannan · Accepted solution]

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.

[mauvasco62]

Great!

Thank you

Mauro