Create a list of months between two dates

user36203

I have a dataset with two date columns: start_date and last_invoice_date for each client. I would like to generate a list of the months between these two dates (not the number of months, but the actual month names).

 

For example, if I have:

client         start_date                  last_invoice_date

  A           Mar 1, 2020                  Jun 1, 2020

  B         January 10, 2018           May 10, 2020

 

I´d like to see this:

 

client         start_date                  last_invoice_date      date

  A           Mar 1, 2020                  Jun 1, 2020              Mar  2020

  A           Mar 1, 2020                  Jun 1, 2020              Apr 2020

  A           Mar 1, 2020                  Jun 1, 2020              May  2020

  A           Mar 1, 2020                  Jun 1, 2020              Jun  2020

........ Same for client B and so on.

 

I´m  trying  this in  sql dataflow, but I kow the logic is wrong. 

 

SELECT a.*, DATE_ADD(`start_date`, INTERVAL @i:=@i+1 MONTH) AS 'Date'
FROM `table` a
, (SELECT @i=0) b
HAVING @i< PERIOD_DIFF(`last_invoice_date`,`start_date`)

 

Is there another approach worth a try?

jaeW_at_Onyx

you need a table with a list of numbers or a table with a list of dates (a date dimension would be easier)

 

Then you can...

 

SELECT

f.*

FROM

Transaction t

JOIN

Date d

ON

d.date BETWEEN t.StartDate and t.EndDate

 

If you only want months then do a subquery

JOIN

(SELECT DISTINCT lastday(date) as date FROM date GROUP BY lastday(date)

 

Just be careful what happens if the contract wasn't active on the last day of the month?  If you want to keep the value then alter your JOIN clause use lastday(enddate).  

 

If you're using numbers then 

 

JOIN numbers

ON date_add(startDate, interval n.num month) <= endDate

 

Again... test what happens at the boundary months and adjust as appropriate.

jaeW_at_Onyx

you need a table with a list of numbers or a table with a list of dates (a date dimension would be easier)

 

Then you can...

 

SELECT

f.*

FROM

Transaction t

JOIN

Date d

ON

d.date BETWEEN t.StartDate and t.EndDate

 

If you only want months then do a subquery

JOIN

(SELECT DISTINCT lastday(date) as date FROM date GROUP BY lastday(date)

 

Just be careful what happens if the contract wasn't active on the last day of the month?  If you want to keep the value then alter your JOIN clause use lastday(enddate).  

 

If you're using numbers then 

 

JOIN numbers

ON date_add(startDate, interval n.num month) <= endDate

 

Again... test what happens at the boundary months and adjust as appropriate.