sum with conditional grouping

Question

I have 2 tables: Departments and Sales. In my department table I have a labor department id and the id of the sales department it is associated to. I also want to allow a department's sales to "rollup" up to another department. I need help with the query to "rollup" the sales.

Here are the tables:

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

Here is the output I would like:

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

As you can see, labor department 1 includes sales for sales department 101 and 103. I have no idea how to do this, though. The query to sum by day, by department is easy enough:

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

but how would I do the "rollup"? I tried putting a case statement in the join like so:

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

but that just dropped the 103 sales department all together. And it doesn't seem the right approach.

Answer 1

select s.[Date], coalesce(d2.LaborDeptid,d1.LaborDeptid) [LaborDept], sum(s.Sales) [TotalSales]
from Sales s
join Departments d1 on s.SalesDept = d1.AssociatedSalesDept
left join Departments d2 on d1.RollUpTo = d2.LaborDeptid
group by s.[Date], coalesce(d2.LaborDeptid,d1.LaborDeptid)

Answer 2

I had to think about this one a bit, but here is one solution:

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