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-11 : 01:31:20

Hi all,
i want to find the number of records from a traffic table for a particular time
for the last 30 days.
Suppose currentdate is 2009-12-11 11:00:00.000, then i want the number of records
for the last 30 days for the time interval(means 11:00:00.000 to means 12:00:00.000),
that means one hour duration from the current time.
it will be searching for these intervals below for the time 2009-12-11 11:00:00.000.

2009-12-11 11:00:00.000 to 2009-12-11 12:00:00.000
2009-12-10 11:00:00.000 to 2009-12-10 12:00:00.000
2009-12-09 11:00:00.000 to 2009-12-09 12:00:00.000
.
.
.
2009-11-11 11:00:00.000 to 2009-11-11 12:00:00.000
2009-11-12 11:00:00.000 to 2009-11-12 12:00:00.000

hope u got the idea.
How the query will be?

thanks in advance

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 01:40:58

select * from table_name
where convert(varchar,date_time_column,101)<=convert(varchar,getdate(),101)
and convert(varchar,date_time_column,101)>=convert(varchar,getdate()-30,101)
and convert(varchar,date_time_column,108)>='11:00:00' and
convert(varchar,date_time_column,108)<='12:00:00'
order by date_time_column

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 01:59:14

select * from table_name
where datepart(hour,date_time_column) between 11 and 12
and date_time_column>=getdate()-30
order by date_time_column

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-12-11 : 02:14:03

thanx for all your quick replies...
senthil query worked ..

Madhivanan, ur query is returning more records which have time greater than 12 like
2009-11-20 12:23:38.0 , as i need between 11 and 12 only..
any way thanks

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 02:24:07

quote:
Originally posted by soorajtnpki

thanx for all your quick replies...
senthil query worked ..

Madhivanan, ur query is returning more records which have time greater than 12 like
2009-11-20 12:23:38.0 , as i need between 11 and 12 only..
any way thanks

Slightly modify madhivanan's query

select * from table_name
where datepart(hour,date_time_column) = 11
and date_time_column>=getdate()-30
order by date_time_column

Its relatively better then mine,I use more no. of convert() function, it may slow down your performance.

Anyway thanks

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-12-11 : 02:30:54

quote:
Originally posted by senthil_nagore

quote:
Originally posted by soorajtnpki

thanx for all your quick replies...
senthil query worked ..

Madhivanan, ur query is returning more records which have time greater than 12 like
2009-11-20 12:23:38.0 , as i need between 11 and 12 only..
any way thanks

Slightly modify madhivan's query

select * from table_name
where datepart(hour,date_time_column) = 11
and date_time_column>=getdate()-30
order by date_time_column

Its relatively better then mine,I use more no. of convert() function, it may slow down your performance.

Anyway thanks

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Thanks senthil,
yep, thats fine for performance, now madhivan's query worked like a charm..thanks for both of your's great help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 04:23:53

Thanks. I missed to notice it

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-12-11 : 05:42:08

And one more modiifcation also,
select * from table_name
where datepart(hour,date_time_column) = 11
and date_time_column>=getdate()-30
and date_time_column<=getdate()
order by date_time_column

cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 06:44:46

quote:
Originally posted by soorajtnpki

And one more modiifcation also,
select * from table_name
where datepart(hour,date_time_column) = 11
and date_time_column>=getdate()-30
and date_time_column<=getdate()
order by date_time_column

cheers

Only if the column has future dates too

Madhivanan

Failing to plan is Planning to fail

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-11 : 08:42:56

Note that if you want anything that falls strictly >= 11:00:00 and < 12:00:00 then where datepart(hour,date_time_column) = 11 works fine. If you want the 'bookends' of each range this will be missing 1 data element (12:00:00). I'm sure the chances of these being in your data is slim, but it is still good to note. It really depends on what range you want.

(11:00:00, 11:59:59) or (11:00:00, 12:00:00)

Just wanted to make that note :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources