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
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 655 Automate
- 170 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 383 Distribute
- 110 Domo Everywhere
- 267 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive