locked
Trying to get Total for each dept, for each Month RRS feed

  • 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


    • Edited by Computermike Wednesday, April 15, 2015 10:09 PM more info
    Wednesday, April 15, 2015 10:06 PM

Answers

  • 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

    • Proposed as answer by Visakh16MVP Thursday, April 16, 2015 5:29 AM
    • Marked as answer by Eric__Zhang Friday, April 24, 2015 2:24 AM
    Thursday, April 16, 2015 2:57 AM
  • 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)

    • Marked as answer by Eric__Zhang Friday, April 24, 2015 2:25 AM
    Thursday, April 16, 2015 3:17 AM

All replies

  • 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

    Wednesday, April 15, 2015 11:23 PM
  • 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

    • Proposed as answer by Visakh16MVP Thursday, April 16, 2015 5:29 AM
    • Marked as answer by Eric__Zhang Friday, April 24, 2015 2:24 AM
    Thursday, April 16, 2015 2:57 AM
  • 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)

    • Marked as answer by Eric__Zhang Friday, April 24, 2015 2:25 AM
    Thursday, April 16, 2015 3:17 AM
  • Yeah, had same thought on way home last night , thanks
    Thursday, April 16, 2015 1:59 PM