0

I want to build the sum from values per hour. See the screenshot:

Screenshot SQL

For this, I used "datepart". Now, I want these values from the datepart in the format from a timestamp. Furthermore I want to fill the other hours of the day, so I want to have 24 rows per day.

Can you help me please?

Gynteniuxas
  • 7,035
  • 18
  • 38
  • 54
Patrik
  • 1

1 Answers1

0

First, create a Numbers Table

Then do this:

select N1.Number, 
       sum(case when P2.Typ = 'outbound' then 1 else 0 end) as Outbound,
       sum(case when P2.Typ = 'inbound' then 1 else 0 end) as Inbound

from Numbers N1
left join #pstntable P2
  on N1.Number = DatePart(hh, P2.Date)
  and P2.Date between @_StartTime and @_EndTime

where N1.Number <= 24

group by N1.Number
Community
  • 1
  • 1
JohnHC
  • 10,935
  • 1
  • 24
  • 40
  • Thanks for your answer! How can I make a numbers table with the following fields: time 2016-09-22 01:00:00 2016-09-22 02:00:00 2016-09-22 03:00:00 2016-09-22 04:00:00 2016-09-22 05:00:00 2016-09-22 06:00:00 2016-09-22 07:00:00 2016-09-22 08:00:00 2016-09-22 09:00:00 and so on.. – Patrik Sep 22 '16 at 07:10
  • @Patrik you wouldn't want to. The idea here is that you have a table of numbers that you pull back that can be applied to ANY day you like. If you want the full date to appear in the column, then do this in the select – JohnHC Sep 23 '16 at 12:53