mardi 8 septembre 2015

How to get the total sales of immediate subordinate based on sales of all subordinate in charge in SQL Server?

the structure is as follows:

ID_EMPLOYEE   ID_EMPLOYEE_BOSS     LEVEL
        -------------------------------------------
            100           NULL                1
            201           100                 2
            202           100                 2
            301           201                 3
            302           201                 3
            303           202                 3
            304           202                 3
            401           302                 4
            402           302                 4
            403           302                 4
            N             N-1                 N

And structure for sales

ID_EMPLOYEE           SALE        DATE
    401               1100.00     2015-06-07
    402               1500.00     2015-06-05
    403               1400.00     2015-06-25
    303               5000.00     2015-05-25
    304               8250.00     2015-05-25

I tried this

WITH Sales_CTE (ID_EMPLOYEE, SALE, MONTHS)
AS
(
    SELECT ID_EMPLOYEE, sum(SALE), DATEPART(mm,DATE)
    FROM SALES
    GROUP BY ID_EMPLOYEE, DATEPART(mm,DATE)
)
SELECT JE.ID_EMPLOYEE, OS.SALE, OS.MONTHS,
    JE.ID_EMPLOYEE_BOSS, OM.SALE, OM.MONTHS
FROM EMPLOYEES AS JE
    JOIN Sales_CTE AS OS
    ON JE.ID_EMPLOYEE= OS.ID_EMPLOYEE
    LEFT OUTER JOIN Sales_CTE AS OM
    ON JE.ID_EMPLOYEE_BOSS= OM.ID_EMPLOYEE
    WHERE ID_EMPLOYEE_BOSS= 302
ORDER BY JE.ID_EMPLOYEE_BOSS;

Currently only I can see sales employee level n-1 immediate, but I want to show as follows. For example employee 100 can see

ID_EMPLOYEE  SALES    MONTH
201           4000    6
202          13250    5

This n levels

Aucun commentaire:

Enregistrer un commentaire