How to sort using DateTime

Question

How to sort using DateTime

gemni7

SSC Veteran

Points: 210
More actions
February 22, 2011 at 9:20 am

#234044

Hi guys,
I am trying to get result from following table where sensorID = j-2933 and result should contain rows only where datetime difference is greater than 5 Minutes
here is sample data
SET DATEFORMAT MDY
Create Table [dbo].[TempByVolume] (
[DateTime] datetimenot null,
SensorID varchar(6) not null,
Temperature float not null,
Volume float not null)
INSERT INTO TempByVolume
SELECT '9/28/2010 7:34:24 PM', 'J-2933', 44.673, 590.23 UNION ALL
SELECT '9/28/2010 7:35:29 PM', 'J-2933', 44.411, 590.02 UNION ALL
SELECT '9/28/2010 7:36:30 PM', 'J-2933', 44.954, 590.33 UNION ALL
SELECT '9/28/2010 7:38:33 PM', 'J-2933', 45.723, 591.04 UNION ALL
SELECT '9/28/2010 7:40:31 PM', 'J-2933', 47.498, 591.22 UNION ALL
SELECT '9/28/2010 7:42:34 PM', 'J-2933', 51.266, 591.41 UNION ALL
SELECT '9/28/2010 7:44:37 PM', 'M-7341', 87.455, 780.44 UNION ALL
SELECT '9/28/2010 7:44:38 PM', 'J-2933', 54.447, 591.61 UNION ALL
SELECT '9/28/2010 7:46:37 PM', 'M-7341', 85.731, 780.26 UNION ALL
SELECT '9/28/2010 7:46:24 PM', 'J-2933', 44.673, 590.23 UNION ALL
SELECT '9/28/2010 7:48:29 PM', 'J-2933', 44.411, 590.02 UNION ALL
SELECT '9/28/2010 7:50:30 PM', 'J-2933', 44.954, 590.33 UNION ALL
SELECT '9/28/2010 7:52:33 PM', 'J-2933', 45.723, 591.04 UNION ALL
SELECT '9/28/2010 7:54:31 PM', 'J-2933', 47.498, 591.22 UNION ALL
SELECT '9/28/2010 7:56:34 PM', 'J-2933', 51.266, 591.41 UNION ALL
SELECT '9/28/2010 7:57:37 PM', 'M-7341', 87.455, 780.44 UNION ALL
SELECT '9/28/2010 7:58:38 PM', 'J-2933', 54.447, 591.61
How this can be accomplished??
I appreciate in Advance

Viewing 15 posts - 1 through 15 (of 15 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Based on your sample data there are no consecutive rows for the sensorID in question with the required 5min gap.

I think it would help a lot if you could post your expected result based on your sample data so we can eliminate any misinterpretation...

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]

gemni7 SSC Veteran Points: 210 More actions · Answer 2

Thanks for your response,

I am using SQL 2005

here are the results i am trying to get

datetime SensorID Temperature Volume

2010-09-28 19:34:24.000J-293344.673590.23

2010-09-28 19:40:31.000J-293347.498591.22

2010-09-28 19:46:24.000J-293344.673590.23

2010-09-28 19:52:33.000J-293345.723591.04

2010-09-28 19:58:38.000J-293354.447591.61

The time difference in above rows is 5min or more.

after first row it skiped 19:35:29, 19:36:30, 19:38:33 and gets 19:40:31 and again it skiped results between 19:40:41 and 19:46:24 and so on

LutzM SSC Guru Points: 107049 More actions · Answer 3

All I could come up with is using the "Quirky Update" method:

You'd need to add another column to your table or copy the data into a temp table if you're not allowed to modify the original table structure.

It's important to read the article refernced by the link in the code together with the related discussion.

DECLARE @Quirky TINYINT ,

@Sequence INT,

@SensorId CHAR(6),

@Date DATETIME;

SET @Sequence = 0

SET @Quirky = NULL

-- This form of the UPDATE statement has some particular rules.

-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

;WITH SafetyCheck AS

(

SELECT

sensorid,

[DATETIME],Quirky,

SEQUENCE = ROW_NUMBER() OVER (ORDER BY sensorid,[DATETIME])

FROM TempByVolume

)

UPDATE t

SET

@Sequence = CASE WHEN SEQUENCE = @Sequence + 1 THEN @Sequence + 1 ELSE 1/0 END,

@Date =

CASE

WHEN DATEDIFF(mi,ISNULL(@Date,'19000101'),[DATETIME])>5 OR SensorId<> @SensorId

THEN [DATETIME]

ELSE @Date END,

@Quirky = Quirky = CASE WHEN @Date=[DATETIME] THEN 1 ELSE 0 END,

@SensorId=SensorId -- ANCHOR COLUMN

FROM SafetyCheck t WITH (TABLOCKX)

OPTION (MAXDOP 1);

SELECT *

FROM TempByVolume

WHERE quirky = 1

ORDER BY SensorId,[DATETIME];

Edit: It's not recommended to use keywords as column names... So you might wan to rename your DATETIME column...

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]

gemni7 SSC Veteran Points: 210 More actions · Answer 4

u sure its running on your machine? giving errors here....

the article you mentioned is a long but very informative, i really need to understand this... thanks for mentioning this article

LutzM SSC Guru Points: 107049 More actions · Answer 5

gemni7 (2/22/2011)
u sure its running on your machine? giving errors here....
the article you mentioned is a long but very informative, i really need to understand this... thanks for mentioning this article

It's hard to help without the detailed error message... But I'd expect you didn't add the Quirky column as mentioned...

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]

gemni7 SSC Veteran Points: 210 More actions · Answer 6

yes, my fault i forget to add quirky

I really appreciate your help

gemni7 SSC Veteran Points: 210 More actions · Answer 7

You are the SQL man Lutz, you saved me from a real big hassel

I thank you and appreciate your great help

rgds,

gem

LutzM SSC Guru Points: 107049 More actions · Answer 8

My pleasure. 😀

But all the honor belongs to Jeff for posting this solution and continuously improving it.

As a side note: do you completely understand how it works and when you should not use this concept? It's important only to use code in production you're completely able to support.

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]

gemni7 SSC Veteran Points: 210 More actions · Answer 9

Hi LutzM,

I have a question about Exclusive Table Lock. What would be SQL servers behavior if my application open connection to insert data into same table while exclusive table lock is active. would sql server hold the query until exclusive lock ends or it return error?

so far I havent seen any error.

LutzM SSC Guru Points: 107049 More actions · Answer 10

gemni7 (3/4/2011)
Hi LutzM,
I have a question about Exclusive Table Lock. What would be SQL servers behavior if my application open connection to insert data into same table while exclusive table lock is active. would sql server hold the query until exclusive lock ends or it return error?
so far I havent seen any error.

That either means you have a high value for LOCK_TIMEOUT (or -1 which means "wait forever") or Jeff's code is really fast.

I'd bet money on the latter. 😀

To answer your original question: Your app will wait until the exclusive lock is released. The time it will wait is controlled by SET LOCK_TIMEOUT. Please see BOL for details and some possible side-effects.

But again, there are at least three reasons Jeffs code is recommended for such scenarios: speed, performance and duration 😀

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]

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 11

gemni7 (3/4/2011)
Hi LutzM,
I have a question about Exclusive Table Lock. What would be SQL servers behavior if my application open connection to insert data into same table while exclusive table lock is active. would sql server hold the query until exclusive lock ends or it return error?
so far I havent seen any error.

How many rows in the table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 12

LutzM (3/4/2011)

gemni7 (3/4/2011)
But again, there are at least three reasons Jeffs code is recommended for such scenarios: speed, performance and duration 😀

Heh... that's a nice thing to say, Lutz. Thanks. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 13

Jeff, got my mail?? (Gentle reminder to Jeff as his PM box if full; apologies for spamming in other's thread :pinch:)

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 14

I did get your email and I responded this morning.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)