August 17, 2011 at 1:22 pm
Perform rate calculation with immediate next record from table
My Temp table #Temp23 has following columns Value, Datetime_record, Result, Month
Value Datetime_record MonthResult
5 08/17 3:05:45 August
4 08/18 3:05:30August
2.5 8/18/2011 3:05August
Need to perform Result = Value1-Value2/Datetime1-Datetime2
Result for 1st row should be = 5-4/15 sec = 0.066
Result for 2nd row should be = 4-2.5/15sec = 0.1
so on and so forth
How can I point Next record in the result set(Temp table). I do not have any ID feild as I am looking at result set..?????
I do have one field common ie Month...
I tried using ROWNUMBER() function to identify and give number to rows in result set. But don't know how to point to next immediate record
and extract information from that record..
Should I be using a self join?? If so How do I seperate out alternate records to different tables and perform calculation??
I want to run update statement something like this
Update #Temp23
SET RESULT = (value1-value2)/datetime_record_1-datetime_record_2
August 17, 2011 at 1:27 pm
Hi
Tables are not sorted by inserted rows. You could insert three rows with INT values 1, 2 and 3 and a, not explicitly ordered, SELECT can return 2, 3, 1.
If you want to join them by next row you need anything to define a unique ordering. If you work with a temp table you can easily add a IDENTITY column, however, if you insert your rows in bulk operation the order of those data is still not guaranteed.
Greets
Flo
August 17, 2011 at 1:46 pm
I actually have a defined crieteria upon which I want to perform rate calculation..
I have the datetime_record ordered by datetime desc...
and I want to pick next immediate datetime column for my rate calculation....
How can I choose that???
August 17, 2011 at 1:57 pm
In this case, you can use a CTE with ROW_NUMBER ordered by your Date and do a self-join.
Here's a little example:
DECLARE @t TABLE (
Value DECIMAL(5,2)
,Date DATETIME
);
INSERT INTO @t VALUES
(4, GETDATE())
,(5, DATEADD(MINUTE, 1, GETDATE()))
,(2.5, DATEADD(MINUTE, 15, GETDATE()));
; WITH cte AS (
SELECT
*
,ROW_NUMBER() OVER (ORDER BY Date ASC) RowNum
FROM @t
)
SELECT
c1.Value - c2.Value
,DATEDIFF(MINUTE, c1.Date, c2.Date)
,*
FROM cte c1
JOIN cte c2 ON c1.RowNum = c2.RowNum - 1;
Greets
Flo
August 17, 2011 at 2:25 pm
@SSCrazy
Thanks for the code...It is working for my dataset...
August 17, 2011 at 2:28 pm
Glad that we could help and thanks for the feedback.
[offtopic]btw: my name is Florian, SQLCrazy is just my (little scary) status here :-P[/offtopic]
August 17, 2011 at 2:42 pm
Could you help me understand how C1 version of table CTE has only 2 rows even if I inserted 3 rows and did select(*) in CTE definition
and C2 version of CTE has rows starting from Rownumber 2 instead of Rownumber 1 ???
My C1 looks like this
4.002011-08-17 16:36:26.4501
5.002011-08-17 16:37:26.4502
C2 looks like this
5.002011-08-17 16:37:26.4502
7.502011-08-17 16:51:26.4503
August 17, 2011 at 2:46 pm
It's because I used a INNER JOIN by "c1.RowNum = c2.RowNum - 1" in my example. Change it to a LEFT JOIN and you will get all rows. In this case you need to handle the NULL values of right side.
If you compare 1 row with its next row you always get an offset of 1 row as result when using an INNER JOIN.
Greets
Flo
August 17, 2011 at 3:00 pm
That was helpful..Thanks...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply