January 6, 2010 at 4:44 am
Hi
I have to compare rows in one column of a table for particular value say 1000 and calculate the difference in time, the last time the column had a value of 1000 to the most recent time the same value is added to the column.
How can this be done?
Thanks for your help.
January 6, 2010 at 4:56 am
Please post DDL and sample data
January 6, 2010 at 5:03 am
Sample data:
reading_timestamp level
06/09/2005 1000
07/09/2005 990
08/09/2005 1300
09/09/2005 1000
Now i have to read values row by row, identify the value 1000 and calculate the difference between the date and time between the previous occurance of value 1000 and current one(in the sample data : 06/09/2005 previous - 09/09/2005 current one)
January 6, 2010 at 5:10 am
something like this ?
select * from <your tab> outer apply (Select top(1)
from <your tab> innertab
where innertab.reading_timestamp < <your tab>.reading_timstamp
and innertab.value >= 1000
order by innertab.reading_timestamp desc)
where <your tab>.value >=1000
January 6, 2010 at 5:55 am
Or are you looking for a reference to the previons date with value=1000
DECLARE @t TABLE (reading_timestamp datetime,value int)
INSERT INTO @t
SELECT '06/09/2005', 1000 UNION ALL
SELECT '07/09/2005', 990 UNION ALL
SELECT '08/09/2005', 1300 UNION ALL
SELECT '09/09/2005', 1000
;with
cte as
(
SELECT
reading_timestamp,
row_number() over(order BY reading_timestamp) AS row
FROM @t
WHERE value =1000
)
SELECT
cte1.reading_timestamp AS start,
cte2.reading_timestamp AS finish,
datediff(dd,cte1.reading_timestamp,cte2.reading_timestamp) AS diff
FROM cte cte1
INNER JOIN cte cte2 ON cte1.row = cte2.row - 1
Side note: Please note that I don't know if you're talkin about data from September 2005 or measurements taken each 9th of the month...
I used the sample data "as provided", leading to the latter interpretation on my system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply