Transformation Question: One record per month

[xyz_1011]

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!

[Lech_Miszkiewicz · Accepted solution]

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;