Time gap query

Question

Post reply

Time gap query

Mishelle

SSC Journeyman

Points: 92
More actions
February 4, 2010 at 9:33 am

#216335

I need to create a query that returns just the rows where the time difference is greater than a specified time (say 15 minutes). Is this possible?
62CDlgAllocationSetSelectEx::OnDelete 7/16/03 11:02:59.000
63CListEditorView::InsertingInsertedMedLists7/16/03 11:15:07.000

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

Lookup "DateADD" function, in BOL, it does exactly what you are looking for.

Cheers,

J-F

BrainDonor SSCoach Points: 19254 More actions · Answer 2

And DATEDIFF - two ways to solve the same problem.

BrainDonor

Steve Hall
Linkedin
Blog Site

Mishelle SSC Journeyman Points: 92 More actions · Answer 3

Thanks! But how would I get either of those to compare with the next record? I know how to do it with two date fields within the same record...

BrainDonor SSCoach Points: 19254 More actions · Answer 4

mbookout (2/4/2010)
Thanks! But how would I get either of those to compare with the next record? I know how to do it with two date fields within the same record...

In which case you need to join the table to itself again. An example along these lines was in another thread:

http://www.sqlservercentral.com/Forums/Topic852880-391-2.aspx#bm853462

BrainDonor

Steve Hall
Linkedin
Blog Site

LutzM SSC Guru Points: 107049 More actions · Answer 5

Yes, it's possible.

You need to do a self join on the table on id=id-1 (or based on row_number(), if there are gaps in the id values).

For a more detailed answer (including code snippet) please provide ready to use sample data as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mishelle SSC Journeyman Points: 92 More actions · Answer 6

Thanks! I should have thought of that. 🙂 That worked beautifully!

Mishelle SSC Journeyman Points: 92 More actions · Answer 7

One more question. I'm running a query that is pulling username, but sometimes there are trailing spaces at the end of the username that I don't want. I've tried SET ANSI_PADDING OFF, but it didn't seem to do what I thought. Any ideas?

LutzM SSC Guru Points: 107049 More actions · Answer 8

mbookout (2/4/2010)
One more question. I'm running a query that is pulling username, but sometimes there are trailing spaces at the end of the username that I don't want. I've tried SET ANSI_PADDING OFF, but it didn't seem to do what I thought. Any ideas?

Did you try RTRIM() function? Or do you store the result in a CHAR() data type rather than VARCHAR()?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mishelle SSC Journeyman Points: 92 More actions · Answer 9

Oh, duh! I tried TRIM (that I found in Books Online) and it told me it wasn't a recognized built-in function and I just assumed RTRIM and LTRIM would do the same... you would think I would have learned many moons ago from Felix Unger about assuming. 🙂

You all have just replaced my husband as my query gurus (much to his relief, I believe). Again, thank you all SO much!

LutzM SSC Guru Points: 107049 More actions · Answer 10

Mishelle (2/4/2010)
...You all have just replaced my husband!...

I truly hope just in terms of SQL stuff!! Family/social life is even more important than SQL! 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mishelle SSC Journeyman Points: 92 More actions · Answer 11

lmu92 (2/4/2010)
Mishelle (2/4/2010)
...You all have just replaced my husband!...
I truly hope just in terms of SQL stuff!! Family/social life is even more important than SQL! 😉

LOL... yes, just in terms of SQL query stuff! He's always telling me (he's the SQL DBA and I'm a systems admin who is the SQL DBA, but doesn't get paid for it...) that I write way more queries than he does...