MySQL Recursive CTE - Date Range Split

Reply
Highlighted
White Belt

MySQL Recursive CTE - Date Range Split

Hi,

 

I am having trouble setting up a recursive CTE with MySQL dataflows, I have seen various articles on recursive dataflows on Domo but do not think they will be much help here, so I am turning to SQL. We currently have budgets corresponding to different campaigns over various date ranges. And example of this is below:

 

Campaign X | Daily Budget | Start Date | End Date

 

The idea would be to convert it to show like this

 

Date | Campaign X | Start Date | End Date | Daily Budget

 

Where date is every day in the Start and End Date range, I currently have this query in an output dataset in the MySQL dataflow:

 

WITH cte AS (
SELECT `Campaign Name`, `Start date`,`End date`
FROM `Budgets'
UNION ALL
SELECT `Campaign Name`, DATEADD(day, 1, `Start date`), `End date`
FROM cte
WHERE `Start date` <= `End date`
)
SELECT * FROM cte;

 

I get the following error message "The database reported a syntax error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cte AS ( SELECT `Campaign Name`, `Start date`,`End date` FROM' at line 1"

 

Any help would be greatly appreciated! Thanks

Tags (3)

Accepted Solutions
Highlighted
Black Belt

If you SELECT VERSION() you'll see which version of MySQL you're developing against, MySQL 5.6, 

 

https://dev.mysql.com/doc/refman/8.0/en/with.html

MySQL 5.6 does not support CTEs :(

 

But if you have a list of Dates (a date dimension) you could accomplish the same thing with a simple JOIN

 

SELECT

a.*

FROM

a

Join 

Date d

on d.date between startDate and endDate

 

or similar.


Jae Wilson
Check out my Domo Training YouTube Channel

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

View solution in original post


All Replies
Highlighted
Black Belt

If you SELECT VERSION() you'll see which version of MySQL you're developing against, MySQL 5.6, 

 

https://dev.mysql.com/doc/refman/8.0/en/with.html

MySQL 5.6 does not support CTEs :(

 

But if you have a list of Dates (a date dimension) you could accomplish the same thing with a simple JOIN

 

SELECT

a.*

FROM

a

Join 

Date d

on d.date between startDate and endDate

 

or similar.


Jae Wilson
Check out my Domo Training YouTube Channel

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

View solution in original post

Highlighted
White Belt

Wow thanks a lot, this did what I needed it to do perfectly, and much much simpler than trying to write a recursive CTE in the first place! Thanks a lot for the help

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.