Select Record where timestamp else non-timestamp r

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

Select Record where timestamp else non-timestamp r

Author

Topic

yidrasil
Starting Member

21 Posts

Posted - 2014-02-10 : 13:49:21

Folks,

I have a table containing many 1000's of records.

Some of those records have a timestamp (date/time in a date/time field)

I am aiming to select ONE record at a time progressing through the set.

If a record WITH a timestamp is within +/- 20 minutes of now then load it

else

Load the next record WITHOUT a timestamp, ordered alphabetically

Can anyone advise the most efficient way to do this. Note I am not using stored procedures at this point.

Thanks in advance

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2014-02-10 : 13:55:31

can you provide some SQL sample of what you want to do? be little explicite please.

--------------------------
Joins are what RDBMS's do for a living

yidrasil
Starting Member

21 Posts

Posted - 2014-02-10 : 14:52:55

I can't offer SQL as I'm stumped on the best/most efficient way ahead. In pseudo-code terms it would be something like

Select DateTimeField
if DateTimefield (is within +/- 10 minute of now)
Load this record 'i.e. load this next as it qualifies
else
if Datetimefield is null or not within +/- 10 minutes of now
Load first record, not within +/- 10 minutes
end if

i.e. I ONLY want to load a positive datetimefield record if it is within +/- 10 minutes of now.

If no qualifiers then load a record with datetimefield set to NULL

or put another way

Skip any record with a Datetimefield value set until that date/time is close to Now.

Hope that helps

yidrasil
Starting Member

21 Posts

Posted - 2014-02-10 : 17:28:26

This is perhaps the closest I've come to it.....

IF EXISTS (SELECT TOP 1 * FROM datatable WHERE DateDiff(mi,FDatetime,getdate()) between 0 and 30)
BEGIN
SELECT TOP 1 * FROM datatable WHERE DateDiff(mi,FDateTime,getdate()) between 0 and 30
END
ELSE
BEGIN
SELECT TOP 1 * FROM Datatable order by CAST(SortOrder AS INT) DESC
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 01:12:36

[code]
SELECT TOP 1 * FROM datatable
ORDER BY CASE WHEN FDateTime BETWEEN DateAdd(mi,-30,getdate()) AND getdate() THEN 1 ELSE 2 END,FDateTime DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

yidrasil
Starting Member

21 Posts

Posted - 2014-02-12 : 09:03:32

Many thanks Visakh..I will take this away and test. Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 07:52:29

welcome
let me know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources