sg4 sg4 - 4 months ago 12
SQL Question

SQL / Excel: Make Excel table of SQL entries per hour

I have a question about moving some SQL query into Excel. So I currently have a table like this (that I got from SQL):

Date | Name
--------------------------
2016-08-09 06:05:22 | Jeff
2016-08-09 06:08:42 | Jeff
2016-08-09 06:43:16 | Jeff
2016-08-09 07:05:22 | Jeff
2016-08-09 07:33:04 | Alex
2016-08-09 09:33:54 | Alex
2016-08-09 10:45:02 | Sara


And what I'm looking to do is build an excel table that displays, separated by hour, how many times each person's name appears. So under a 6 o'clock column I'd want there to be a 3 next to Jeff and 0's for everyone else. At 7 I'd want a 1 for Jeff and a 1 for Alex. And at 9 and 10, I'd want a 1 for Alex and a 1 for Sara, respectively.

Here's what I'd like it to look like:

| 06 | 07 | 08 | 09 | 10 | 11
----------------------------------
Jeff | 3 | 1 | 0 | 0 | 0 | 0
Alex | 0 | 1 | 0 | 1 | 0 | 0
Sara | 0 | 0 | 0 | 0 | 1 | 0

Answer

Here is a quick and dirty way to get that result in SQL:

SELECT
    name,
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 0 THEN 1 END) AS [00],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 1 THEN 1 END) AS [01],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 2 THEN 1 END) AS [02],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 3 THEN 1 END) AS [03],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 4 THEN 1 END) AS [04],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 5 THEN 1 END) AS [05],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 6 THEN 1 END) AS [06],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 7 THEN 1 END) AS [07],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 8 THEN 1 END) AS [08],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 9 THEN 1 END) AS [09],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 10 THEN 1 END) AS [10],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 11 THEN 1 END) AS [11],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 12 THEN 1 END) AS [12],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 13 THEN 1 END) AS [13],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 14 THEN 1 END) AS [14],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 15 THEN 1 END) AS [15],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 16 THEN 1 END) AS [16],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 17 THEN 1 END) AS [17],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 18 THEN 1 END) AS [18],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 19 THEN 1 END) AS [19],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 20 THEN 1 END) AS [20],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 21 THEN 1 END) AS [21],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 22 THEN 1 END) AS [22],
    COUNT(CASE WHEN DATEPART(HOUR,[Date]) = 23 THEN 1 END) AS [23]
FROM
    mytable -- Replace with the name of your table
GROUP BY
    name

This will create a column for each hour base on if the individual (name) has a record within that hour.


Here it is using the PIVOT syntax:

WITH mycte AS (
   -- To make sure that hours with no individuals (`name`), we full join to all possible hours (0-23)
SELECT
    hr.hour_number,
    mt.name,
    mt.[Date]
FROM
    (
    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)
    ) hr (hour_number)
FULL JOIN
    mytable mt
    ON  (hr.hour_number = DATEPART(HOUR,mt.datet))
GROUP BY
    hr.hour_number,
    mt.name,
    mt.[Date]
)

-- This is the actual pivot. Had we not done the work above, hours were the value was 0 for all `name`s would not appear
SELECT
    name,
    [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],
    [13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]
FROM
    mycte
PIVOT
    (
    COUNT([Date]) FOR hour_number 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]
        )
    ) p
WHERE
    name IS NOT NULL -- This ensure only records associated to a name is returned