Trying to get Total for each dept, for each Month

Question

Try to join these two tables so I get a total for each dept for each month...   4 departments times 12 months, should get 48 rows returned.  My real example spans more than a year.  Maybe I am tired but having a hard time with this.  Code below.  Any help appreciated.

declare @Calendar table
(StartYear int,
StartMonth int)

insert into  @Calendar
values
(2015, 1),
(2015, 2),
(2015, 3),
(2015, 4),
(2015, 5),
(2015, 6),
(2015, 7),
(2015, 8),
(2015, 9),
(2015, 10),
(2015, 11),
(2015, 12)

declare @MyDeptNums table
(Dept varchar(1),
fMonth int,
fYear int,
sales int)

insert into @MyDeptNums
values
('A',7,2015, 10),
('B', 8, 2015, 11),
('B', 8, 2015, 22),
('C',9, 2015, 50),
('C',4, 2015, 25)

--Below does not work, what does?????????

select
D.Dept,
C.StartYear,
C.StartMonth,
isnull(sum(D.Sales),0) as 'Total'
from
@Calendar C
left join @MyDeptNums D on C.StartMonth = D.fMonth
        and C.StartYear = D.fYear
group by
D.Dept,
C.StartYear,
C.StartMonth
order by
D.Dept,
C.StartYear,
C.StartMonth

Answer 1

This works, but I think could be better.  Don't like the distinct in sum sales.

;with cte_A as
(select C.StartYear, C.StartMonth, D.Dept from 
@Calendar C cross join @MyDeptNums D)

select   A.Dept, A.StartMonth, A.StartYear, isnull(sum(distinct B.sales),0) as 'x' from cte_A A
left join @MyDeptNums B on A.Dept = B.Dept and A.StartMonth = B.fMonth and A.StartYear = B.fYear
group by  A.Dept, A.StartMonth, A.StartYear

Answer 2

Distinct will give you a problem if you ever have multiple rows in the @MyDeptNums table with the same fYear, fMonth, and sales values.  You can eliminate that problem with

;With Dept As
(Select Distinct Dept
From @MyDeptNums),
Sales As
(Select Dept, fYear, fMonth, Sum(sales) As Total
From @MyDeptNums
Group By Dept, fYear, fMonth)
Select d.Dept, c.StartYear, c.StartMonth, IsNull(Total, 0) As Total
From @Calendar c
Cross Join Dept d
Left Join Sales s On d.Dept = s.Dept And c.StartYear = s.fYear And c.StartMonth = s.fMonth
Order By Dept, StartYear, StartMonth;

Tom

Answer 3

This works, but I think could be better.  Don't like the distinct in sum sales.

;with cte_A as
(select C.StartYear, C.StartMonth, D.Dept from 
@Calendar C cross join @MyDeptNums D)

select   A.Dept, A.StartMonth, A.StartYear, isnull(sum(distinct B.sales),0) as 'x' from cte_A A
left join @MyDeptNums B on A.Dept = B.Dept and A.StartMonth = B.fMonth and A.StartYear = B.fYear
group by  A.Dept, A.StartMonth, A.StartYear

Put the distinct in your CTE?

(select distinct C.StartYear, C.StartMonth, D.Dept from  
@Calendar C cross join @MyDeptNums D)

Answer 4

Yeah, had same thought on way home last night , thanks