date sql problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

date sql problem

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2009-08-25 : 06:10:14

Hi,

i have the following query that returns all the records from today:

select session_id, arrival_time
from clicky_recent_visitors
where datediff( d,arrival_time,getdate()) = 0
and clicky_site_id = 1111
order by arrival_time asc

reutrns the following rows..

44574359 2009-08-25 00:55:00.000
44583557 2009-08-25 01:15:00.000
44585678 2009-08-25 01:20:00.000
44601391 2009-08-25 01:54:00.000
44616471 2009-08-25 02:28:00.000
44633733 2009-08-25 03:08:00.000
44652914 2009-08-25 03:54:00.000
44652883 2009-08-25 03:54:00.000
44653137 2009-08-25 03:55:00.000
44653116 2009-08-25 03:55:00.000
44654052 2009-08-25 03:57:00.000
44701540 2009-08-25 06:20:00.000
44722655 2009-08-25 07:31:00.000
44725062 2009-08-25 07:38:00.000
44767940 2009-08-25 09:34:00.000
44770612 2009-08-25 09:41:00.000
44774221 2009-08-25 09:49:00.000
44775052 2009-08-25 09:51:00.000
44785953 2009-08-25 10:18:00.000
44789254 2009-08-25 10:25:00.000
44790263 2009-08-25 10:28:00.000

What I would like to do is to feed the info inot a graph where the x-axis is 24 hrs.. eg. 1am, 2am, 3am ......10pm, 11p,

and in the y-axis i will have the number of sessions for that hour. basically will round up or down.

so no of visitors against time.

I'm struggling to maipulate the data to do this. Does anyone have aby ideas and can help?

thx

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-25 : 06:22:24

You can GROUP BY the hour part of the date, if it's always just one day at a time,
Something like this:
select DATEPART(hh, arrival_time), count(*)
from clicky_recent_visitors
where datediff( d,arrival_time,getdate()) = 0
and clicky_site_id = 1111
group by DATEPART(hh,arrival_time)
order by DATEPART(hh,arrival_time)

zubair
Yak Posting Veteran

67 Posts

Posted - 2009-08-25 : 08:43:54

Thanks! that helped a lot.

is there a way to display the first column i.e. the hour in a format such as 11am, 12pm, 1pm etc?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources