Alter statement to find differences between rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Alter statement to find differences between rows

Author

Topic

ejvigil
Starting Member

2 Posts

Posted - 2007-12-18 : 20:32:08

I need to write a little alter statement to find the difference between time stamps.

Below are four rows from the table.

12pm
2pm
4pm
7pm

so I need a column that will show what the difference between each timestamp.

So it would look like:
TIMESTAMP DIFFERENCE COLUMN
12pm 0
2pm 120
4pm 120
7pm 180

Any and all help is greatly appreciated!!!!

Thanks in advance!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-18 : 21:29:50

Is there a PK on the table? If so post some data for that column too..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ejvigil
Starting Member

2 Posts

Posted - 2007-12-19 : 11:13:56

There isn't any other information on the table I can. It is just personal information that isn't relevant.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 11:37:24

[code]DECLARE @Sample TABLE (dt DATETIME)

INSERT @Sample
SELECT '12pm' UNION ALL
SELECT '2pm' UNION ALL
SELECT '4pm' UNION ALL
SELECT '7pm'

SELECT dt,
DATEDIFF(MINUTE, dt2, dt)
FROM (
SELECT s1.dt,
COALESCE((SELECT MAX(s2.dt) FROM @Sample AS s2 WHERE s2.dt < s1.dt), s1.dt) AS dt2
FROM @Sample AS s1
) AS d[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources