Sum in tricky way, how to make it beautiful??

• mario17

  SSCertifiable

  Points: 6079

  More actions

  May 14, 2014 at 12:44 pm

  #288771

  Hi, all

  I preparing ds for my report and need some intelligent summation, every monthID will go into column Group, so each CustID will be on single line, but outside the group I need to provide Sum for each Quarter,

  for now I have this code, but I have a feeling that I can do better with some Analytical f?

  I appreciate your help, I dont' think I can do this in rdl and our site company is to do as much as possible in sql.

  Thanks

  Chao

  M

  ;WITH T AS (

  SELECT 1111 CustID, 201201 MonthID, 10001 Amt UNION

  SELECT 1111 CustID, 201202 MonthID, 20002 Amt UNION

  SELECT 1111 CustID, 201203 MonthID, 30003 Amt UNION

  SELECT 1111 CustID, 201204 MonthID, 40004 Amt UNION

  SELECT 1111 CustID, 201205 MonthID, 50005 Amt UNION

  SELECT 700 CustID, 201202 MonthID, 7000 Amt UNION

  SELECT 700 CustID, 201206 MonthID, 9000 Amt

  )

  SELECT

  CustID,

  MonthID,

  Amt,

  ( SELECT SUM(Amt) FROM T T2 WHERE T1.CustID = T2.CustID

  AND T2.MonthID BETWEEN 201201 AND 201203) Q1_Sum,

  ( SELECT SUM(Amt) FROM T T2 WHERE T1.CustID = T2.CustID

  AND T2.MonthID BETWEEN 201204 AND 201206) Q2_Sum

  FROM T T1

  ----------------------

  CustIDMonthIDAmtQ1_SumQ2_Sum

  700201202700070009000

  700201206900070009000

  1111201201100016000690009

  1111201202200026000690009

  1111201203300036000690009

  1111201204400046000690009

  1111201205500056000690009

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 14, 2014 at 12:50 pm

  #1713583

  Using the CASE and OVER clauses instead of reading three times the same table. 😉

  ;WITH T AS (

  SELECT 1111 CustID, 201201 MonthID, 10001 Amt UNION

  SELECT 1111 CustID, 201202 MonthID, 20002 Amt UNION

  SELECT 1111 CustID, 201203 MonthID, 30003 Amt UNION

  SELECT 1111 CustID, 201204 MonthID, 40004 Amt UNION

  SELECT 1111 CustID, 201205 MonthID, 50005 Amt UNION

  SELECT 700 CustID, 201202 MonthID, 7000 Amt UNION

  SELECT 700 CustID, 201206 MonthID, 9000 Amt

  )

  SELECT

  CustID,

  MonthID,

  Amt,

  SUM(CASE WHEN MonthID BETWEEN 201201 AND 201203

  THEN Amt

  ELSE 0 END) OVER(PARTITION BY CustID) AS Q1_Sum,

  SUM(CASE WHEN MonthID BETWEEN 201204 AND 201206

  THEN Amt

  ELSE 0 END) OVER(PARTITION BY CustID) AS Q2_Sum

  FROM T T1

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• mario17

  SSCertifiable

  Points: 6079

  More actions

  May 15, 2014 at 4:03 pm

  #1714047

  Thansk much, Luis.

  Best

  Mario