finding absolut values from cumulative values in sql server

Question

Post reply

finding absolut values from cumulative values in sql server

harsh-280401

SSC Journeyman

Points: 88
More actions
August 9, 2006 at 1:10 am

#116045

Hi,
I have a table on which I have a column which is datetime.
It stores datetimes stamps.
Every record will have unique timestamp and every record's timestamp will be greater than all last records.
But for a report , I want difference between timestamps of each consectuive records.
for example , let's say my first three timestamps are like this .
2006-08-01 15:00:00
2006-08-01 18:00:00
2006-08-01 20:00:00
So in result set , I want.
null (there is no records previous to 2006-08-01 15:00:00)
3 (difference between 2006-08-01 18:00:00 and 2006-08-01 15:00:00)
2 (difference between 2006-08-01 20:00:00 and 2006-08-01 18:00:00)
Difference between timestamp and timestamp previous to that one.
How can I get this?
Thanks ,
Harsh

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rob Farley SSCrazy Points: 2739 More actions · Answer 1

In SQL2005, you could easily do this. Alter this a little to make it fit your needs.

with records as (select *, row_number() over (order by timestamp) as rn from table1)

select r1.*, datediff(second,r1.timestamp,r2.timestamp) as secondsdifferent

from records r1 left join records r2 on r2.rn = r1.rn + 1

In SQL2000, when you don't have CTEs or the OVER clause, you'll need to work out a different way of doing this. Like...

select t1.*, datediff(second, (select min(t2.timestamp) from table1 t2 where t2.timestamp < t1.timestamp), t1.timestamp) as secondsdifferent

from table1

Which I guess isn't all that awful really... but doesn't perform nearly so well as using the OVER clause.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Harsh

Try the script below (not tested). Somebody else may be able to come up with something that doesn't involve a temp table...

John

CREATE TABLE #Table (TableID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Event VARCHAR(50), MyTimestamp datetime)

INSERT INTO #Table

SELECT Event, MyTimestamp FROM MyTable

ORDER BY MyTimestamp

SELECT t1.Event, t1.MyTimestamp, t2.MyTimestamp AS PreviousTimestamp

FROM #Table t1 JOIN #Table

ON t1.TableID = t2.TableID + 1

ORDER BY t1.MyTimestamp

Rob Farley SSCrazy Points: 2739 More actions · Answer 3

Ew... stupid mistake.

select t1.*, datediff(second, (select max(t2.timestamp) from table1 t2 where t2.timestamp < t1.timestamp), t1.timestamp) as secondsdifferent

from table1

Max if we're looking at earlier records. min if we're looking at later records.

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Andrew Clarke-161005 Mr or Mrs. 500 Points: 587 More actions · Answer 4

Here is a way of doing it without any temporary table, using a correlated subquery (probably slower!). I've used the original test data t make a simple test table. Note that you normally want the difference in milliseconds rather than hours if you are using this to measure the time taken by blocks of code!

DECLARE @timestamps TABLE (timestampentry datetime)
INSERT INTO @Timestamps SELECT '2006-08-01 15:00:00'
INSERT INTO @Timestamps SELECT '2006-08-01 18:00:00'
INSERT INTO @Timestamps SELECT '2006-08-01 20:00:00'

SELECT DATEDIFF (hour,
       (SELECT MAX( s.timestampentry) 
               FROM @Timestamps s 
               WHERE s.timeStampEntry<t.timestampentry),
       timestampentry)
       FROM @Timestamps t