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

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • 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