Create a list of months between two dates

user36203
user36203
in Dataflows

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?

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Answer ✓

    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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories