Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


datetime based query


datetime based query

Author
Message
gemni7
gemni7
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
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, 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

LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7011 Visits: 13559
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
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
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, 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

LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7011 Visits: 13559
Glad to help, Wayne :-D

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
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6261 Visits: 10404
LutzM (9/29/2010)
Glad to help, Wayne :-D

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, 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

gemni7
gemni7
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search