Return result set for every hour of the day

[Sl1ver]

Hi im writing a sql query that needs to be grouped the hours in the day e.g
if its 10 am then it should have 10 different groups with data where the date and hour corresponds to that

i've tried

datepart(hour, getdate())

but it does not return the required result, any help?

[Echidna]

Hi,

I would suggest setting up a table with the hours in the day 1 - 24 (or am/pm) or something similar.
then creating a view from this as a left outer join to the table, the datepart(hour,g etdate()) function can then be used to limit the number of records produced.

so something like this could work.

SELECT timename, eventname from
(
SELECT timename, eventname from tbltime LEFT OUTER JOIN tblevent on tbltime.timeid = Datepart(hour,tblevent.datetimefield)
GROUP BY (tbltime.timename, Datepart(hour,tblevent.datetimefield))
HAVING (Datepart(hour,tblevent.datetimefield) <= datepart(hour,getdate())
)

it needs something to refer against.

Hope this helps

Cheers

Leon

[ck9663]

Here's a sub-query. You can make this a view, a table variable, a derived table or a CTE and use it to join to your table.

select colorder as hour_of_day
from
   (select colorder
   from syscolumns c1 
      inner join (select top 1 c2.id from syscolumns c2 group by c2.id having count(*) >= 24) c3 on c1.id = c3.id
   where colorder <= 24) hours_in_day

OR a UNION ALL

select * 
from (
   select 1 as hour_of_day
   union all
   select 2
   union all
   select 3
   union all
   select 4
   union all
   select 5
   union all
   select 6
   union all
   select 7
   union all
   select 8
   union all
   select 9
   union all
   select 10
   union all
   select 11
   union all
   select 12) hours_in_day

Happy Coding!!!

~~ CK