How to get a date basis hourly count by query?

Question

In SQL

How to write a count by query based on 1 hour time duration interval for date time.

I can write query based on date. But date and Time Stamp would be a Pivot structure.

How to get a date basis hourly count by query ?

2017-01-02 05:27:10.117   Sam
2017-01-02 15:27:10.117   Sam
2017-01-03 06:27:10.116   Ben
2017-01-03 06:28:10.119   Aam

Expected Output

CountByHour       05:00 to 06:00 AM    06:00 to 07:00 AM   15:00 to 16:00
2017-01-02          1                      0                  1
2017-01-03          0                      2                  0

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Marc L., Jan 23 '17 at 17:51
@MarcL. there might be a possibility on every single postings. this is little different .. — goofyui, Jan 23 '17 at 17:52
i really dont need pivot program which i can do that .. i need only the hourly basis count — goofyui, Jan 23 '17 at 17:53
@goofyui It's a particular *case*, but is just a specific application of the general case outlined: you just have to apply PIVOT (possibly dynamically) to the particular data-set. This question becomes one that is so specific that it is not really of use in the future. If you have further trouble with PIVOT, then please ask another question. — Marc L., Jan 23 '17 at 18:00
@MarcL. I have written bunch of Pivot. If you dont understand the question, please ignore it. I am not looking for Pivot help. I need assistance on count by hourly basis sql syntax help. — goofyui, Jan 23 '17 at 18:09
Sorry, that wasn't clear from the "expected output" in your question. That's why SO has comments! — Marc L., Jan 23 '17 at 18:23

Lukas Eder · Accepted Answer · 2017-01-23T18:39:45.783

This query will return the pivot table in more or less the desired form:

WITH t(d, h, n) AS (
  SELECT CAST(t AS DATE) AS d, h, COUNT(CASE WHEN h = datepart(hh, t) THEN 1 END) AS n
  FROM (
    VALUES 
      ('2017-01-02 05:27:10.117', 'Sam'),
      ('2017-01-02 15:27:10.117', 'Sam'),
      ('2017-01-03 06:27:10.116', 'Ben'),
      ('2017-01-03 06:28:10.119', 'Aam')
  ) AS t(t, s)
  CROSS JOIN (
    VALUES (0) , (1) , (2) , (3) , (4) , (5),
           (6) , (7) , (8) , (9) , (10), (11),
           (12), (13), (14), (15), (16), (17),
           (18), (19), (20), (21), (22), (23)
  ) AS h(h)
  GROUP BY CAST(t AS DATE), h
)
SELECT *
FROM t
PIVOT (
  sum(n) FOR h IN (
    [0] , [1] , [2] , [3] , [4] , [5], 
    [6] , [7] , [8] , [9] , [10], [11], 
    [12], [13], [14], [15], [16], [17],
    [18], [19], [20], [21], [22], [23]
  )
) t
ORDER BY d

Explanation:

The CROSS JOIN operation combines each timestamp (t) with each hour (h) in a day (hardcoded here) by forming a cartesian product. I've recently blogged about the utility of CROSS JOIN in this article here.
The COUNT() aggregate function the counts the number of rows (n) (timestamps) that match an actual hour.
Finally, PIVOT transposes the h column into individual columns

hats off, thank you so much !! How to replace the cartesian product in the values field with my actual column names.. — goofyui, Jan 23 '17 at 23:01
@goofyui: Can you be precise? I'm not sure what you mean by `col1` and `col2` — Lukas Eder, Jan 24 '17 at 08:20
this works .. i have already implemented the query logic in our system.. thank you so much !! — goofyui, Mar 12 '17 at 16:09

score 1 · Answer 2 · answered Jan 24 '17 at 10:35

I solved this using CAST,CONVERT AND CASE Syntaxs. Thought may be useful to you.

SELECT CONVERT(VARCHAR(10),COL1,108)
       ,ISNULL(SUM(CASE WHEN CAST(SUBSTRING(CONVERT(VARCHAR(30),CAST (COL1 AS DATETIME),108),1,2) AS INT) >= 5 AND CAST(SUBSTRING(CONVERT(VARCHAR(30),CAST (COL1 AS DATETIME),108),1,2) AS INT) < 6
        THEN 1 END),0) AS [5_to_6]
       ,ISNULL(SUM(CASE WHEN CAST(SUBSTRING(CONVERT(VARCHAR(30),CAST (COL1 AS DATETIME),108),1,2) AS INT) >= 6 AND CAST(SUBSTRING(CONVERT(VARCHAR(30),CAST (COL1 AS DATETIME),108),1,2) AS INT) < 7
        THEN 1 END),0) AS [6_to_7] 
       ,ISNULL(SUM(CASE WHEN CAST(SUBSTRING(CONVERT(VARCHAR(30),CAST (COL1 AS DATETIME),108),1,2) AS INT) >= 15 AND CAST(SUBSTRING(CONVERT(VARCHAR(30),CAST (COL1 AS DATETIME),108),1,2) AS INT) < 16
        THEN 1 END),0) AS [15_to_16]

FROM 
            (VALUES('2017-01-02 05:27:10.117','Sam'),
            ('2017-01-02 15:27:10.117','Sam'),
            ('2017-01-03 06:27:10.116','Ben'),
            ('2017-01-03 06:28:10.119','Aam')) TC (COL1,COL2)
GROUP BY CONVERT(VARCHAR(10),COL1,108)
GO

score 0 · Answer 3 · answered Jan 23 '17 at 18:22

To be able to pivot the data, the date and hour need to reside in separate columns. You could do this within the context of your PIVOT; as an intermediate result (with names made up):

select cast(eventdatetime as date) eventdate
  , datepart(hh, eventdatetime) eventhour
  , count(*) eventcount 
from mytable 
group by cast(eventdatetime as date), hour(eventdatetime)

This would give the result (from your example)

eventdate  eventhour
2017-01-02 5   1
2017-01-02 15   1
2017-01-03 6 2

Your should then be able to pivot on that.

How to get a date basis hourly count by query?

3 Answers3

Explanation: