sg4 - 9 months ago 35

SQL Question

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