SQL Server 2012 – Pivot on Date and Count Values by Hour

pivotsql serversql-server-2012

I hope this makes sense. I have a database which logs the day and time a row was inserted. I have a query which will select and count the number of insertions per hour.
I would like to pivot this so that it shows the date as rows and the hours as columns.

eg: 

  DateTime      Hour1     Hour2    Hour3    |
+-----------------------------------------+
| 2016-01-01     11        10      35      |
| 2016-01-02     12        15      25      |

My current query shows as : 

  DateTime      Hour       total    |
+-----------------------------------+
| 2016-01-01     1          11      |
| 2016-01-01     2          10

Query :

SELECT CAST([datetime_received] as date) AS ForDate,
       DATEPART(hour,[datetime_received]) AS OnHour,
       COUNT(*) AS Totals
FROM [claims]
where [datetime_received] between '2018-08-03 00:00:00.000' and '2018-08-03 23:59:00.000'
GROUP BY CAST([datetime_received] as date),
       DATEPART(hour,[datetime_received])

I do not have much experience with pivot queries so any help would be appreciated.

thanks

Best Answer

Ok, so after few trial and error queries I managed to solve this.

It may not be the best way but it seems to work fine.

select *
from 
(
  select CAST([datetime_received] as date) AS ForDate,   DATEPART(hour,[datetime_received]) AS OnHour,     COUNT(*) AS Totals
  from [claims]
  where [datetime_received] between '2019-07-01 00:00:00.000' and '2019-07-31 23:59:00.000'
  GROUP BY CAST([datetime_received] as date),
       DATEPART(hour,[datetime_received])


) src
pivot
(
  sum(Totals) 
  for OnHour 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]) 
) piv 
order by ForDate

If any one can help me refine this i will appreciate it.

Thanks

Related Question