Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

datetime based query Expand / Collapse
Author
Message
Posted Wednesday, September 29, 2010 2:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 11:34 AM
Points: 8, Visits: 24
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
Post #995594
Posted Wednesday, September 29, 2010 3:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 6,594, Visits: 8,882
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #995626
Posted Wednesday, September 29, 2010 3:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 7,161, Visits: 13,228
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #995631
Posted Wednesday, September 29, 2010 3:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 6,594, Visits: 8,882
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #995634
Posted Wednesday, September 29, 2010 3:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 7,161, Visits: 13,228
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #995636
Posted Wednesday, September 29, 2010 4:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 6,594, Visits: 8,882
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #995657
Posted Wednesday, September 29, 2010 4:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 30, 2011 11:34 AM
Points: 8, Visits: 24
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
Post #995658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse