Autogenerate dates in a certain range

[#1]

Hello! I have data in the format of the table below:

start_date	company_name	pay_model
2015-10-01	Company1	1
2015-12-01	Company2	1
2016-01-01	Company3	2
2015-12-01	Company4	6

I want to expand the table to showing dates up to 2016-01-01 for each company:

start_date	company_name	pay_model
2015-10-01	Company1	1
2015-11-01	Company1	1
2015-12-01	Company1	1
2016-01-01	Company1	1
2015-12-01	Company2	1
2016-01-01	Company2	1
2016-01-01	Company3	2
2015-12-01	Company4	6
2016-01-01	Company4	6

So far, i've auto generated the dates but all my solutions gives each company_name the same time span, Company1 spans from 2015-10-01 to 2016-01-01 (which is correct) but so does also the other companies.

Any suggestions on how this can be achieved?

[swuehl · Accepted solution]

Try something like

SET DateFormat = 'YYYY-MM-DD';

LOAD Date(Addmonths(start_date,iterno()-1)) as start_date, company_name, pay_model

FROM ....

WHILE AddMonths(start_date,iterno()-1) <= makedate(2016,1);

[#3]

That worked exactly as I wanted it to! Thank you so much for shortening my evening working session