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.
| Author |
Topic |
|
yidrasil
Starting Member
21 Posts |
 Posted - 2011-07-04 : 17:33:24
|
| Folks,Would appreciate some knowledgeable help with a problem I'm facing.I need to run a T-SQL query (runs every minute) from the start of each hour forward (e.g. 07:00:00) that will retrieve new records added since that hour began.So, each minute ALL new records since the start of the hour are retrieved. Therefore at H+59 Minutes 59 Seconds I have all the records for that hour in a table. The process then restarts again for the following hour and moves forward.I'm currently looking at the salient section as so.................WHERE DATEDIFF(hh,getdate(),Finishdate) = 0Finishdate = my date/time fieldHowever, I'm unsure whether this will do the job or whether there is a more efficient/recommended way.No doubt there are multiple ways to achieve this but I'm relatively new to T-SQL so would be grateful for suggestions?.Yid |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-04 : 17:54:12
|
| Datediff gives the number of boundaries crossed between the two values so will give what you want. The problem is that it cannot use an index apar from scanning.where finishdate >= dateadd(hh,datediff(hh,0,getdate()),0) and finishdate < dateadd(hh,datediff(hh,0,getdate())+1,0) migt make better use of an index on finishdate.You could also add another column which is just populated with the hour - maybe yyyymmddhh or create a computed column or vies and index it.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
yidrasil
Starting Member
21 Posts |
Posted - 2011-07-05 : 02:14:59
|
| Many thanks for the reply Nigel. Appreciate your comments regarding indexing...will look at what my be possible. Unfortunately, it's a (rather old) inherited database so will explore what the addition of indexing might do to performance of the database as a whole.Thanks again.....Yid |
|
|
|
|
|
|
|
|
Retrieve All Records added since start of Hour