query help - 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)

query help

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-12-04 : 06:24:22

I have a site traffic table, i want to get hourly report for a particular date.
table and sample data are as follows.

traffic -table
columns
date,
sessionID,
item,
refer,
cookie,
ip,
browser

i wanted to get the data on an hourly basis for the particular date(let it be current date)

output

Hour total page views avg page views sessions
12 am 2 3 3
1 am 1 1 2
2 am .
3 am .
4 am
.
.
.
12 pm
1 pm
2 pm
.
.
.
11 pm

pls help me
thanx in advance

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-12-04 : 07:13:51

Any similar queries

creieru
Starting Member

12 Posts

Posted - 2009-12-04 : 07:16:00

something like this...

select datepart(Hh,date), count(sessionID)
from traffic
group by datepart(Hh,date)
where year(date) = year(getdate()) and month(date) = month(getdate()) and day(date) = day(getdate())

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 07:16:49

Something like

select
dateadd(hour,datediff(hour,0,date),0),
count(*),
avg(some_col)
from your_table
group by dateadd(hour,datediff(hour,0,date),0)

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-12-04 : 07:28:49

any other ways

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-04 : 07:35:17

quote:
Originally posted by soorajtnpki

any other ways

Why?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources