Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
i have the following data (structure):
Contract_ID | Beg_Dt | End_Dt |
123 | 01.01.2024 | 31.12.2024 |
456 | 15.05.2023 | 14.05.2024 |
I need to transform this data into one record per month per contract. Like this:
Contract_ID | Beg_Dt | End_Dt | Beg_Dt_Calc | End_Dt_Calc | Month_Num |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 31.01.2024 | 1 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 29.02.2024 | 2 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 31.03.2024 | 3 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 30.04.2024 | 4 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 31.05.2024 | 5 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 30.06.2024 | 6 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 31.07.2024 | 7 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 31.08.2024 | 8 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 30.09.2024 | 9 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 31.10.2024 | 10 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 30.11.2024 | 11 |
1234 | 01.01.2024 | 31.12.2024 | 01.01.2024 | 31.12.2024 | 12 |
456 | 15.05.2023 | 14.05.2024 | 15.05.2023 | 31.05.2023 | 1 |
456 | 15.05.2023 | 14.05.2024 | 01.06.2023 | 31.06.2023 | 2 |
456 | 15.05.2023 | 14.05.2024 | 01.07.2023 | 30.07.2023 | 3 |
456 | 15.05.2023 | 14.05.2024 | 01.08.2023 | 31.08.2023 | 4 |
456 | 15.05.2023 | 14.05.2024 | 01.09.2023 | 30.09.2023 | 5 |
456 | 15.05.2023 | 14.05.2024 | 01.10.2023 | 31.10.2023 | 6 |
456 | 15.05.2023 | 14.05.2024 | 01.11.2023 | 30.11.2023 | 7 |
456 | 15.05.2023 | 14.05.2024 | 01.12.2023 | 31.12.2024 | 8 |
456 | 15.05.2023 | 14.05.2024 | 01.01.2024 | 31.01.2024 | 9 |
456 | 15.05.2023 | 14.05.2024 | 01.02.2024 | 29.02.2024 | 10 |
456 | 15.05.2023 | 14.05.2024 | 01.03.2024 | 31.03.2024 | 11 |
456 | 15.05.2023 | 14.05.2024 | 01.04.2024 | 30.04.2024 | 12 |
456 | 15.05.2023 | 14.05.2024 | 01.05.2024 | 14.05.2024 | 13 |
Please note the difference in breaking the data down for contract 456 which starts / ends not at the beginning / end of a month. Please also note that a contract can last more / less that 12 months.
I believe this can be done using a loop, but im for whatever reason not successful yet - any help / hint / suggestion is much appreciated.
Many thanks in advance!
That should do it:
temp:
Load
Contract_ID
,Beg_Dt
,End_Dt
Inline
[
Contract_ID, Beg_Dt, End_Dt
1234, 01-01-2024, 31-12-2024
456, 15-05-2023, 14-05-2024
]
;
Final:
Load
Contract_ID
,Beg_Dt
,End_Dt
,Date(Floor(If(IterNo()=1,
AddMonths(Beg_Dt,IterNo()-1),
MonthStart(AddMonths(Beg_Dt,IterNo()-1))))) as Beg_Dt_Calc
,Date(Floor(If(AddMonths(Beg_Dt,IterNo()-1)>End_Dt,
End_Dt,
MonthEnd(AddMonths(Beg_Dt,IterNo()-1))))) as End_Dt_Calc
,IterNo() as Month_Num
Resident
temp
While
AddMonths(Beg_Dt,IterNo()-1)<=MonthEnd(End_Dt)
;
Drop Table temp;
That should do it:
temp:
Load
Contract_ID
,Beg_Dt
,End_Dt
Inline
[
Contract_ID, Beg_Dt, End_Dt
1234, 01-01-2024, 31-12-2024
456, 15-05-2023, 14-05-2024
]
;
Final:
Load
Contract_ID
,Beg_Dt
,End_Dt
,Date(Floor(If(IterNo()=1,
AddMonths(Beg_Dt,IterNo()-1),
MonthStart(AddMonths(Beg_Dt,IterNo()-1))))) as Beg_Dt_Calc
,Date(Floor(If(AddMonths(Beg_Dt,IterNo()-1)>End_Dt,
End_Dt,
MonthEnd(AddMonths(Beg_Dt,IterNo()-1))))) as End_Dt_Calc
,IterNo() as Month_Num
Resident
temp
While
AddMonths(Beg_Dt,IterNo()-1)<=MonthEnd(End_Dt)
;
Drop Table temp;