add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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 Smith');
INSERT INTO PrevYear
VALUES ('ls22', '100', '2018-05-01', 'Maggie Samuels');

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 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'))
months_to_pull employeename IA
Jan-2018 Maggie Samuels 100
Jan-2019 Maggie Samuels 100
Feb-2018 null 0
Feb-2019 null 0
Mar-2018 null 0
Mar-2019 null 0
Apr-2018 null 0
Apr-2019 null 0
May-2018 Maggie Samuels 100
May-2019 Maggie Samuels 100
Jun-2018 null 0
Jun-2019 null 0
Jul-2018 null 0
Jul-2019 null 0
Aug-2018 null 0
Aug-2019 null 0
Sep-2018 null 0
Sep-2019 null 0
Oct-2018 null 0
Oct-2019 null 0
Nov-2018 null 0
Nov-2019 null 0
Dec-2018 null 0
Dec-2019 null 0