September 29, 2010 at 2:34 pm
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
September 29, 2010 at 3:15 pm
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
September 29, 2010 at 3:27 pm
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.
September 29, 2010 at 3:34 pm
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
September 29, 2010 at 3:41 pm
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';
September 29, 2010 at 4:42 pm
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
September 29, 2010 at 4:44 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply