SQL Recursion with Parent / Child for Employees

Question

I have a little question: I have a view and want to make a small recursive (my approach) call to a table called employee. There are the employees and a column "reportsTo", which gives the EmployeeId of the respective superior or parent.

There is also an invoice table with all purchases made by customers. The customers have a direct contact person / seller (SupportRepId). Now the annual sales of the employees are to be listed, as well as those of the direct employees. (Reverse ReportTo!)

create view employee_sales_v as 
   select employee.LastName, employee.ReportsTo, sum(invoice.total) sales, 
   year(invoice.InvoiceDate) year 
from employee 
join customer on customer.SupportRepId = employee.EmployeeId 
join invoice on invoice.CustomerId = customer.CustomerId
group by employee.LastName, year(invoice.InvoiceDate);

How can I get these sales?

SampleData:

CREATE TABLE `Customer`
(
    `CustomerId` INT NOT NULL AUTO_INCREMENT,
    `SupportRepId` INT,
    CONSTRAINT `PK_Customer` PRIMARY KEY  (`CustomerId`)
);

CREATE TABLE `Employee`
(
    `EmployeeId` INT NOT NULL AUTO_INCREMENT,
    `LastName` NVARCHAR(20) NOT NULL,
    `FirstName` NVARCHAR(20) NOT NULL,
    `ReportsTo` INT,
    CONSTRAINT `PK_Employee` PRIMARY KEY  (`EmployeeId`)
);

CREATE TABLE `Invoice`
(
    `InvoiceId` INT NOT NULL AUTO_INCREMENT,
    `CustomerId` INT NOT NULL,
    `InvoiceDate` DATETIME NOT NULL,
    `Total` NUMERIC(10,2) NOT NULL,
    CONSTRAINT `PK_Invoice` PRIMARY KEY  (`InvoiceId`)
);

INSERT INTO `Employee` (`LastName`, `FirstName`, `ReportsTo`) VALUES (N'HGF', N'POI', 0);
INSERT INTO `Employee` (`LastName`, `FirstName`, `ReportsTo`) VALUES (N'XYZ', N'ABC', 1);

INSERT INTO `Customer` (`SupportRepId`) VALUES (2);
INSERT INTO `Customer` (`SupportRepId`) VALUES (2);

INSERT INTO `Invoice` (`CustomerId`, `InvoiceDate`, `Total`) VALUES (1, '2013/1/1', 1.98);
INSERT INTO `Invoice` (`CustomerId`, `InvoiceDate`, `Total`) VALUES (2, '2009/10/2', 3.96);
INSERT INTO `Invoice` (`CustomerId`, `InvoiceDate`, `Total`) VALUES (2, '2010/5/3', 5.94);

Answer 1

... as well as those of the direct employees.

Since only the sales total of the direct employees are needed, recursion is not required in this case.

SQL:

WITH CTE AS 
    (SELECT SupportRepId, year(i.InvoiceDate) AS YEAR, sum(i.total) total
    FROM Customer c 
    INNER JOIN Invoice i on i.CustomerId = c.CustomerId
    GROUP BY year(i.InvoiceDate), SupportRepId) ---CREATE CTE
SELECT 
    e1.LastName, 
    e1.ReportsTo, 
    t1.year,
    IFNULL(cte1.total,0.00) AS `My Sales`,
    IFNULL(t2.total,0.00) AS `Reportees Sales`
FROM Employee e1 
INNER JOIN (SELECT DISTINCT year(i.InvoiceDate) AS year from Invoice i) t1 on 1 = 1 --- ALL DISTINCT YEARS
LEFT JOIN CTE cte1 on e1.EmployeeId = cte1.SupportRepId and t1.year = cte1.year
LEFT JOIN 
    (SELECT 
     e2.ReportsTo, cte2.year, sum(cte2.total) total 
     FROM Employee e2
     INNER JOIN CTE cte2 ON e2.EmployeeId = cte2.SupportRepId
    GROUP BY e2.ReportsTo, cte2.year) t2  --- GET EMPLOYEE DETAILS
    ON t2.ReportsTo = e1.EmployeeId AND t1.year = t2.year
--- WHERE cte1.year is not null || t2.year is not null
ORDER by e1.LastName, t1.year

Details:

• --- CREATE CTE : Fetch employees and their sales for each year. Created this subquery as cte since it needs to be reused
• --- ALL DISTINCT : Fetch all the distinct year the invoice is generated. This will allow us to generate report for employees even if they have not made any sales on a year. (If that is not required, uncomment WHERE cte1.year is not null || t2.year is not null to filter out employees and reportes with no sales for a year).
• --- GET EMPLOYEE DETAILS : This subquery fetches the sales details of the reportees.

Demo:

Fiddle Link over here. I have added additional test cases for validation.

---

Update: Query update for pre MySQL 8

SELECT 
    e1.LastName, 
    e1.ReportsTo, 
    t1.year,
    IFNULL(cte1.total,0.00) AS `My Sales`,
    IFNULL(t2.total,0.00) AS `Reportees Sales`
FROM Employee e1 
INNER JOIN (SELECT DISTINCT year(i.InvoiceDate) AS year from Invoice i) t1 on 1 = 1 --- ALL DISTINCT YEARS
LEFT JOIN 
(SELECT SupportRepId, year(i.InvoiceDate) AS YEAR, sum(i.total) total
    FROM Customer c 
    INNER JOIN Invoice i on i.CustomerId = c.CustomerId
    GROUP BY year(i.InvoiceDate), SupportRepId) cte1 on e1.EmployeeId = cte1.SupportRepId and t1.year = cte1.year
LEFT JOIN 
    (SELECT 
     e2.ReportsTo, cte2.year, sum(cte2.total) total 
     FROM Employee e2
     INNER JOIN ((SELECT SupportRepId, year(i.InvoiceDate) AS YEAR, sum(i.total) total
                  FROM Customer c 
                  INNER JOIN Invoice i on i.CustomerId = c.CustomerId
                  GROUP BY year(i.InvoiceDate), SupportRepId)) cte2 
     ON e2.EmployeeId = cte2.SupportRepId
    GROUP BY e2.ReportsTo, cte2.year) t2  --- GET EMPLOYEE DETAILS
    ON t2.ReportsTo = e1.EmployeeId AND t1.year = t2.year
--- WHERE cte1.year is not null || t2.year is not null
ORDER by e1.LastName, t1.year

Fiddle link: https://www.db-fiddle.com/f/td8RaWwYr7AKp1wWGfJ8yK/3