Create a list of months between two dates
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
-
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"1
Answers
-
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"1
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 659 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 816 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 776 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 82 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive