Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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);
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);
That worked exactly as I wanted it to! Thank you so much for shortening my evening working session