Retrieving 1st Record in time range Else

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

General SQL Server Forums

New to SQL Server Programming

Retrieving 1st Record in time range Else

Author

Topic

yidrasil
Starting Member

21 Posts

Posted - 2014-01-05 : 11:23:35

Folks,

I have a date/time value (dd/MM/yyyy HH:mm:ss) stored in a field as part of each record in a dataset.

I would like to achieve the following via a single T-SQL query if possible (efficiency is not an issue as the dataset is small)

On query run;

Check if any records in the dataset are within 10 minutes + or - of the current system time (using the date/time value referenced above)

if none

Load the first record

I would really appreciate some suggestions on this as I'm on a tight timescale with a ton still to do.

Much appreciate the experts input.......

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-05 : 11:59:21

Please define "Load the first record".
If you "just" want the record closest to current date/time, try this:

select top(1) *
  from yourtable
 order by abs(datediff(ss,getdate(),yourdatetimefield))

yidrasil
Starting Member

21 Posts

Posted - 2014-01-05 : 12:29:36

Thanks for your help Bitsmed.

Within the dataset there are some records with a datetime and thers that are null in that field.

I'm aiming to load a record (if exists) within +/- x minutes
Else
Load the first record that doesn't have a populated datetime field

In effect then
Check dataset for any record with a datetime within +/- x minutes of now
If None Exist
Load the First Record that doesn't have a populated datetime field.

Hope that provides more detail

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-05 : 12:45:51

Maybe this:

select top(1) *
  from yourtable
 where abs(datediff(ss,getdate(),isnull(yourdatetimefield,dateadd(ss,10*60,getdate())))<=10*60
 order by abs(datediff(ss,getdate(),isnull(yourdatetimefield,dateadd(ss,10*60,getdate())))<=10*60
         ,id desc

In the order by section (the red field), put in another field from your table, ex. if you have an id field - then this will get the highest id, in case no date/time is available.

yidrasil
Starting Member

21 Posts

Posted - 2014-01-05 : 13:03:22

Thanks though I'm getting a syntax error before each < in both where and order by clauses for some reason. Looks like an extra bracket required in the Where but I can't yet see the problem with Order By

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-05 : 13:10:31

Ups my mistake. Try this:

select top(1) *
  from yourtable
 where abs(datediff(ss,getdate(),isnull(yourdatetimefield,dateadd(ss,10*60,getdate()))))<=10*60
 order by abs(datediff(ss,getdate(),isnull(yourdatetimefield,dateadd(ss,10*60,getdate())))
         ,id desc

yidrasil
Starting Member

21 Posts

Posted - 2014-01-05 : 13:17:13

Ok, works now and I'm checking it out..initially looks good!.

Many thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources