How To Write A Query With A CTE And A Left Join

I am trying to have a calendar table with my CTE and set it up so that my dates in my query display like this

Jan 18
Jan 19
Feb 18
Feb 19

Now this is my DDL and this is the query I attempted, but in MySql Workbench I’m getting the error that there is an error in my sql somewhere.

This is the exact error:

Query Error: Error: ER_PARSE_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 ‘RECURSIVE cte_months_to_pull AS ( SELECT DATE_FORMAT(@start_date, ‘%Y-%m-01” at line 1

Can someone assist?

If it’s easier this is a SQL Fiddle of everything http://sqlfiddle.com/#!9/300f9d/1

CREATE TABLE PrevYear (


`EmployeeNumber` char(8) NOT NULL,
  `SaleAmount` int DEFAULT NULL,
  `SaleDate` date NOT NULL,
  `EmployeeName` char(17) NOT NULL
); 

CREATE TABLE CurrentYear (
  `EmployeeNumber` char(8) NOT NULL,
  `SaleAmount` int DEFAULT NULL,
  `SaleDate` date NOT NULL,
  `EmployeeName` char(17) NOT NULL
); 

INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-01-10', 'Maggie Samuels');
INSERT INTO CurrentYear
VALUES ('ea12', '199', '2019-01-13', 'Sam Stoner');
INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-03-01', 'Jake Jolel');
INSERT INTO CurrentYear
VALUES ('ls22', '100', '2019-05-01', 'Maggie Samuels');

INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-01-10', 'Maggie Samuels');
INSERT INTO PrevYear
VALUES ('ea12', '199', '2018-01-13', 'Sam Stoner');
INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-03-01', 'Sam Stoner');
INSERT INTO PrevYear
VALUES ('ls22', '100', '2018-05-01', 'Maggie Samuels');

And this is the query I try:

    SET @start_date = '20190102';
SET @number_of_months = 12;
WITH RECURSIVE
cte_months_to_pull AS (
    SELECT DATE_FORMAT(@start_date, '%Y-%m-01')
         - INTERVAL @number_of_months MONTH AS month_to_pull
    UNION ALL
    SELECT month_to_pull + INTERVAL 1 MONTH
    FROM cte_months_to_pull
    WHERE month_to_pull < @start_date + INTERVAL @number_of_months - 2 MONTH
)
SELECT Date_format(saledate, '%m-%Y') AS Month,
               employeename,
               Sum(saleamount)                AS IA
        FROM   currentyear
        WHERE  employeename = 'Maggie Samuels'
        GROUP  BY Date_format(saledate, '%m-%Y'), employeename
        UNION ALL
        SELECT Date_format(saledate, '%m-%Y') AS Month,
               employeename,
               Sum(saleamount)                AS IA
        FROM   prevyear
        WHERE  employeename = 'Maggie Samuels'
        GROUP  BY Date_format(saledate, '%m-%Y'), employeename
LEFT JOIN cte_months_to_pull (
    Select DATE_Format(month_to_pull, '%b %y')
    FROM cte_months_to_pull
) AS YRS ON month_to_pull = saledate
ORDER BY MONTH(month_to_pull), YEAR(month_to_pull)

Answer

As I can see you are using MySQL version older than 8.0 which doesn’t support RECURSIVE CTEs. I have tried your query with some minor updates on 8.0 and it worked fine –

WITH RECURSIVE
cte_months_to_pull AS (
    SELECT DATE_FORMAT(@start_date, '%Y-%m-01')
         - INTERVAL @number_of_months MONTH AS month_to_pull
    UNION ALL
    SELECT month_to_pull + INTERVAL 1 MONTH
    FROM cte_months_to_pull
    WHERE month_to_pull < @start_date + INTERVAL @number_of_months - 2 MONTH
)
SELECT YRS.months_to_pull
      ,T.employeename
      ,COALESCE(T.IA, 0) IA
FROM (SELECT DATE_Format(month_to_pull, '%b-%Y') months_to_pull
      FROM cte_months_to_pull
      ORDER BY months_to_pull
     ) AS YRS
LEFT JOIN (SELECT Date_format(saledate, '%b-%Y') AS `Month`
                 ,employeename
                 ,Sum(saleamount) AS IA
           FROM   CurrentYear
           WHERE  employeename = 'Maggie Samuels'
           GROUP  BY Date_format(saledate, '%b-%Y'), employeename
           UNION ALL
           SELECT Date_format(saledate, '%b-%Y')
                 ,employeename
                 ,Sum(saleamount)
           FROM   PrevYear
           WHERE  employeename = 'Maggie Samuels'
           GROUP  BY Date_format(saledate, '%b-%Y'), employeename) T
ON YRS.months_to_pull = T.`Month`
ORDER BY month(STR_TO_DATE(CONCAT('01-',months_to_pull), '%d-%b-%Y'))
        ,YEAR(STR_TO_DATE(CONCAT('01-',months_to_pull), '%d-%b-%Y'))

Here is the Fiddle

Since there is no expected output, I have only tried till running the query.