Sum by date of the week

• legeboka

  Mr or Mrs. 500

  Points: 558

  More actions

  April 13, 2015 at 12:29 pm

  #302937

  Hi guys,

  I’m stuck on the simple query and can’t figure out. I have table #Test1 with 2 columns: amount and date. I need to update amount column by setting Monday amount with SUM of previous Saturday and Sunday. So all weekend amounts will roll into Monday date while Sat and Sun will be set to zero. I was trying to use ROW_NUMBER function but can’t get it right

  Thank you for your help

  IF (SELECT OBJECT_ID('tempdb..#Test1'))is not null

  DROP TABLE #Test1

  SELECT Amount, TranDate

  INTO #Test1

  FROM (

  SELECT 10.00,'2015-02-17 00:00:00.000' UNION ALL

  SELECT 1.00, '2015-02-18 00:00:00.000' UNION ALL

  SELECT 2.00, '2015-02-19 00:00:00.000' UNION ALL

  SELECT 3.00, '2015-02-20 00:00:00.000' UNION ALL

  SELECT 4.00, '2015-02-21 00:00:00.000' UNION ALL

  SELECT 5.00, '2015-02-22 00:00:00.000' UNION ALL

  SELECT 6.00, '2015-02-23 00:00:00.000' UNION ALL

  SELECT 7.00, '2015-02-24 00:00:00.000' UNION ALL

  SELECT 8.00, '2015-02-25 00:00:00.000' UNION ALL

  SELECT 9.00, '2015-02-26 00:00:00.000' UNION ALL

  SELECT 11.00, '2015-02-27 00:00:00.000' UNION ALL

  SELECT 12.00, '2015-02-28 00:00:00.000'

  ) d (Amount, TranDate);

  SELECT * FROM #Test1

  So the outcome of the query should be

  10.00, '2015-02-17 00:00:00.000' --Tu

  1.00, '2015-02-18 00:00:00.000' --Wed

  2.00, '2015-02-19 00:00:00.000' --Th

  3.00, '2015-02-20 00:00:00.000' --Fr

  0, '2015-02-21 00:00:00.000' --Sa

  0, '2015-02-22 00:00:00.000' --Su

  15.00,'2015-02-23 00:00:00.000' --Mo

  7.00, '2015-02-14 00:00:00.000' --Tu

  And So on

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  April 13, 2015 at 1:20 pm

  #1790619

  Something like this could help.

  SELECT

  ISNULL( t.Amount, 0) Amount,

  t1.TranDate

  FROM #Test1 t1

  LEFT JOIN

  (

  SELECT SUM(Amount) AS Amount,

  CASE WHEN DATENAME(DW, TranDate) = 'Saturday' THEN DATEADD( dd, 2, TranDate)

  WHEN DATENAME(DW, TranDate) = 'Sunday' THEN DATEADD( dd, 1, TranDate)

  ELSE TranDate END AS TranDate

  FROM #Test1

  GROUP BY

  CASE WHEN DATENAME(DW, TranDate) = 'Saturday' THEN DATEADD( dd, 2, TranDate)

  WHEN DATENAME(DW, TranDate) = 'Sunday' THEN DATEADD( dd, 1, TranDate)

  ELSE TranDate END

  ) t ON t1.TranDate = t.TranDate

  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

• legeboka

  Mr or Mrs. 500

  Points: 558

  More actions

  April 13, 2015 at 1:24 pm

  #1790620

  thank you. This will work