2

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

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.