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

Expand|Select|Wrap|Line Numbers

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,getdate()) function can then be used to limit the number of records produced.

so something like this could work.

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

  
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

Expand|Select|Wrap|Line Numbers

 
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

Return result set for every hour of the day

re: Return result set for every hour of the day

re: Return result set for every hour of the day

Similar topics

More channels