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

by

Introduction

This article will discuss how to use SQL to group data by 36 hours. We will look at how to use the GROUP BY clause to group data by 36 hours, as well as how to use the DATEADD function to add 36 hours to a date. We will also discuss how to use the DATEDIFF function to calculate the difference between two dates in 36-hour increments. Finally, we will look at how to use the CAST function to convert a date to a 36-hour interval.

Solution

SELECT
DATEADD(HOUR, DATEDIFF(HOUR, 0, timestamp), 0) AS start_time,
DATEADD(HOUR, DATEDIFF(HOUR, 0, timestamp) + 36, 0) AS end_time,
COUNT(*) AS count
FROM table
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, timestamp), 0)
ORDER BY start_time; 


--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] 


SELECT 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, [timestamp]), 0) AS [timestamp], 
    COUNT(*) AS [count] 
FROM 
    [table] 
GROUP BY 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, [timestamp]), 0) 
HAVING 
    DATEDIFF(HOUR, MIN([timestamp]), MAX([timestamp])) <= 36 
ORDER BY 
    [timestamp]

This SQL query can be used to group data by 36 hours. It will take the timestamp column from a table and group it by the hour, while also counting the number of entries in each group. The query also includes a HAVING clause which ensures that the difference between the minimum and maximum timestamp is no more than 36 hours. Finally, the query is ordered by the timestamp column.