[Solved] Sql to group by 36 hours [closed]

by 


--Set up table and data
DECLARE @ATable TABLE ([date] DATETIME, [Count] INT)
INSERT @ATable( date, Count )
SELECT '2015-05-14 01:00:00', 1
UNION ALL
SELECT '2015-05-15 02:00:00', 2
UNION ALL
SELECT '2015-05-15 20:00:00', 3
UNION ALL
SELECT '2015-05-16 03:00:00', 4

-- Query
SELECT
  d.[date],
  ( -- This subquery returns the sum of counts for the 36 hours
    SELECT
      SUM(a.[count])
    FROM
      @ATable a
    WHERE
      a.[date] < DATEADD(hour, 36, d.[date])
      AND
      a.[date] >= d.[date]
  ) AS [count]
FROM
  ( -- This subquery returns a list of unique dates
    SELECT
      DATEADD(dd, DATEDIFF(dd, 0, [date]), 0) AS [date]
    FROM
      @ATable
    GROUP BY
      DATEADD(dd, DATEDIFF(dd, 0, [date]), 0)
  ) AS d
ORDER BY
  d.date

Please note that your data and required results DO NOT MATCH, As you have been told repeatedly. The above yields the following results:

date                          count
----------------------- -----------
2015-05-14 00:00:00.000           3
2015-05-15 00:00:00.000           9
2015-05-16 00:00:00.000           4

solved Sql to group by 36 hours [closed]