datetime based query

Question

datetime based query

gemni7

SSC Enthusiast

Points: 160
More actions
September 29, 2010 at 2:34 pm

#229870

Hi guys,
I am getting hardtime to arrange my results. i think its better to ask someone.
I have a simple TempByVolume Table having 4 columns as follow
1. DateTime. datatype is datetime
2. SensorID datatype is varchar
3. Temperature datatype is float
4. Volume datatype is float
following is the table what it looks like
DateTime------------ --SensorID -Temp --Volume
9/28/2010 7:34:24 PM| J-2933| 44.673| 590.23
9/28/2010 7:35:29 PM| J-2933| 44.411| 590.02
9/28/2010 7:36:30 PM| J-2933| 44.954| 590.33
9/28/2010 7:38:33 PM| J-2933| 45.723| 591.04
9/28/2010 7:40:31 PM| J-2933| 47.498| 591.22
9/28/2010 7:42:34 PM| J-2933| 51.266| 591.41
9/28/2010 7:44:37 PM| M-7341| 87.455| 780.44
9/28/2010 7:44:38 PM| J-2933| 54.447| 591.61
9/28/2010 7:46:37 PM| M-7341| 85.731| 780.26
I want to get temperature and volume results between 9/28/2010 7:35:29 PM and 9/28/2010 7:42:34 PM including these times for SensorID J-2933 but I also want previous temp and volume readings in result.
I want to sort results as below:
SensorID | ----DateTime---- | Temp | PrevTemp | Volume | PrevVolume
J-2933 | 9/28/2010 7:35:29 PM | 44.411 | 44.673 | 590.02 | 590.23
J-2933 | 9/28/2010 7:36:30 PM | 44.954 | 44.411 | 590.33 | 590.02
J-2933 | 9/28/2010 7:38:33 PM | 45.723 | 44.954 | 591.04 | 590.33
J-2933 | 9/28/2010 7:40:31 PM | 47.498 | 45.723 | 591.22 | 591.04
J-2933 | 9/28/2010 7:42:34 PM | 51.266 | 47.498 | 591.41 | 591.22
J-2933 | 9/28/2010 7:44:38 PM | 54.447 | 51.266 | 591.61 | 591.41
How can I get previous temp and volume results in my output??
Its getting hard..... need help
Regards

Viewing 7 posts - 1 through 6 (of 6 total)

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

WayneS SSC Guru Points: 95409 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 2

I would use a CTE together with ROW_NUMBER() OVER(PARTITION BY SensorID ORDER BY DateTime) AS row and do a self (outer?) join on it based on Sensorid and cte1.row=cte2.row-1

If you need more details please see Wayne's post and help us help you.

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]

WayneS SSC Guru Points: 95409 More actions · Answer 3

The attached code works for me; however I'm having problems posting it.

Edit: since Lutz posted my code, I've deleted the attachment here. See the following post of Lutz' for what I was trying to post.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 4

Glad to help, Wayne 😀

SET DATEFORMAT MDY;

-- See how this starts off with a table and data in it?

-- If you had provided us the data in this format,

-- it would have made things easier for all of the

-- volunteers on this site to help you out.

DECLARE @TempByVolume Table (

[DateTime] datetime,

SensorID varchar(6),

Temperature float,

Volume float);

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;

WITH CTE AS

(

-- order rows by datetime, starting over for each sensor.

SELECT *,

RN = ROW_NUMBER() OVER (PARTITION BY SensorID ORDER BY [DateTime])

FROM @TempByVolume

)

SELECT t1.SensorID, t1.[DateTime], t1.Temperature, t2.Temperature, t1.Volume, t2.Volume

FROM CTE t1

JOIN CTE t2

ON t1.SensorID = t2.SensorID

AND t2.RN = t1.RN - 1

WHERE t1.[DateTime] between '9/28/2010 7:35:29 PM' and '9/28/2010 7:42:34 PM'

AND t1.SensorID = 'J-2933';

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]

WayneS SSC Guru Points: 95409 More actions · Answer 5

LutzM (9/29/2010)
Glad to help, Wayne 😀

Thanks Lutz.

The last few days, I've had issues trying to post to some threads... it just won't accept my code, but it will take it as an attachment. Quite frustrating.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

gemni7 SSC Enthusiast Points: 160 More actions · Answer 6

Thanks guys, I just read an excellent article on CTE that solved my problem.

here is the link

http://www.sqlservercentral.com/articles/T-SQL/62159/

Thanks for posting code too. i just finished my code that is similar to yours.

SQL ServerCentral.com rolls