сумма с условной группировкой

У меня есть 2 таблицы: отделы и продажи. В моей таблице отделов у меня есть идентификатор отдела труда и идентификатор отдела продаж, с которым он связан. Я также хочу, чтобы продажи одного отдела «сводились» к другому отделу. Мне нужна помощь с запросом на "сведение" продаж.

Вот таблицы:

TABLE = Departments
LaborDeptid | AssociatedSalesDept | RollUpTo
1                    101              0   
2                    102              0
3                    103              1
4                    104              0

TABLE = Sales
Date      | Sales | SalesDept
1/1/2014    10.00     101 
1/1/2014    10.00     101
1/1/2014    10.00     102
1/1/2014    10.00     102
1/1/2014    10.00     103
1/1/2014    10.00     103
1/1/2014    10.00     104
1/1/2014    10.00     104

Вот результат, который я хотел бы:

OUTPUT
Date      | LaborDept | TotalSales
1/1/2014       1         40.00
1/1/2014       2         20.00
1/1/2014       4         20.00

Как видите, отдел труда 1 включает продажи для отделов продаж 101 и 103. Однако я понятия не имею, как это сделать. Запрос на суммирование по дням, по отделам достаточно прост:

select 
    Date,
    LaborDept,
    sum(sales) as TotalSales
from sales s
inner join departments d on s.SalesDept = d.AssociatedSalesDept 
group by Date,LaborDept`

а как бы мне сделать "свертку"? Я попытался поместить оператор case в соединение следующим образом:

select 
    sum(sales) as TotalSales,
    Date,
    LaborDept
from sales s
inner join departments d on s.SalesDept = case when d.RollUpTo <> 0 then 
(select AssociatedSalesDept 
from departments
where d.RollUpTo = LaborDeptID)
else d.AssociatedSalesDept end
group by Date,LaborDept

но это просто привело к полному падению отдела продаж 103. И это не кажется правильным подходом.


sql sql-server group-by sum sql-server-2008-r2
person Timothy Carter    schedule 25.06.2014    source источник
comment
Большое спасибо за все 3 ответа. Мне понравился подход cte в концепции, но он не сработал для меня (вероятно, я что-то сделал не так, я полагаю). В итоге решение DavidN было простым для понимания и хорошо работало примерно на 20 000 строк данных. Спасибо.   -  person Timothy Carter    schedule 26.06.2014

Ответы (2)


arrow_upward
2
arrow_downward

Мне пришлось немного подумать об этом, но вот одно решение:

WITH cte AS (
SELECT CASE WHEN RollUpTo = 0 THEN LaborDeptId ELSE RollUpTo END AS LaborDeptId, AssociatedSalesDept
FROM departments)

SELECT s.date, d.LaborDeptid, SUM(s.Sales) AS TotalSales
FROM Sales s 
INNER JOIN cte d ON s.SalesDept = d.AssociatedSalesDept
GROUP BY s.date, d.LaborDeptid
person Dave.Gugg    schedule 25.06.2014
comment
В моих тестах это было немного лучше, чем у DavidN, потому что у него было только одно чтение для каждой таблицы вместо двойного чтения таблицы отделов. - person Dave.Gugg; 25.06.2014

arrow_upward
1
arrow_downward 

    SELECT s.Date, 
           d.LaborDeptId,
           SUM(Sales) + ISNULL((SELECT 
                            CASE Departments.RollUpTo
                                WHEN 0 THEN SUM(0)
                                ELSE SUM(Sales)
                            END
                          FROM Departments
                    INNER JOIN Sales ON Departments.AssociatedSalesDept = Sales.SalesDept
                         WHERE Departments.RollUpTo = d.LaborDeptId
                           AND Departments.RollUpTo <> 0
                      GROUP BY Departments.RollUpTo), 0) AS TotalSales
      FROM Departments d 
INNER JOIN Sales s ON d.AssociatedSalesDept = s.SalesDept
     WHERE d.RollUpTo = 0
  GROUP BY s.Date, d.LaborDeptId, d.RollUpTo;

Другой вариант использования подзапроса. Хотя мне больше нравится DavidN.

person drneel    schedule 25.06.2014