Splitting a row into multiple rows according to a date range

[enricocamerin]

Hello,

I have an original table 

cod, quantity, date_start, date_end

DE2520, 10 , 01-01-2020, 31-10-2020

and this is the final table I need to have. generating row depending on how much month there is between start date and end date

cod, quantity, month, year

DE2520, 1 , 1,2020,

DE2520, 1 , 2,2020,

DE2520, 1 , 3,2020,

DE2520, 1, 4,2020,

DE2520, 1 , 5,2020,

DE2520, 1 , 6,2020,

DE2520, 1, 7,2020,

DE2520,1, 8,2020,

DE2520,1, 9,2020,

DE2520,1 , 10,2020

thanks

[PrashantSangle · Accepted solution]

FYI,

Test:

Load *,

((year(new_date_end)+month(new_date_end))-(year(date_start)+month(date_start)))+1 as diff_in_mnths;

Load *,

date(MonthStart(date_end)) as new_date_end;

Load cod, 

quantity, 

Date(Date#(date_start,'DD-MM-YYYY')) as date_start,

  Date(Date#(date_end,'DD-MM-YYYY')) as date_end

Inline [

cod, quantity, date_start, date_end

DE2521, 9 , 01-01-2021, 30-09-2021

DE2520, 10 , 01-01-2020, 31-10-2020

];

NoConcatenate

Final:

Load *,

Year(date) as year,

    num(Month(date)) as month;

Load *,

quantity / diff_in_mnths as new_qty,

AddMonths(date_start,IterNo() - 1) as date

Resident Test

While AddMonths(date_start,IterNo() - 1) < date_end

;

drop table Test;

Exit Script;

Regards,

Prashant Sangle

[enricocamerin]

Thanks Prashant great.

This is what I needed

Enrico