Sum the difference of the consecutive 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 2005 Forums

Transact-SQL (2005)

Sum the difference of the consecutive rows

Author

Topic

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-09-12 : 01:31:38

HI,

I have a table with a Date-Time column with the below values

10:30
10:35
11:00
11:10

I need to calculate the sum of difference of consecutive rows , for example
10:35 - 10:30 = 5
11:00 - 10:35 = 25
11:10 - 11:00 = 10

And then total the sum = 5+25+10 = 40

I am trying out the best way to get the sum, help needed.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-12 : 01:35:51

[code]DECLARE @TABLE TABLE
(
[time] datetime
)

INSERT INTO @TABLE
SELECT '10:30' UNION ALL
SELECT '10:35' UNION ALL
SELECT '11:00' UNION ALL
SELECT '11:10'

SELECT [the SUM] = SUM(DATEDIFF(minute, t1.[time], t2.[time]))
FROM
(
SELECT [time], row = row_number() OVER (ORDER BY [time])
FROM @TABLE
) t1
INNER JOIN
(
SELECT [time], row = row_number() OVER (ORDER BY [time])
FROM @TABLE
) t2
ON t1.row = t2.row - 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-09-12 : 01:41:23

Thanks a lot,

I was trying with cursors but this is a easy and quick solution.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 01:45:10

And if you don't want to calculate the row_number() twice

;WITH Yak (Time, Row)
AS (
	SELECT	[time], row_number() OVER (ORDER BY [time])
	FROM	@TABLE
)

SELECT	    [the SUM] = SUM(DATEDIFF(minute, t1.[time], t2.[time]))
FROM        Yak AS t1	
INNER JOIN  Yak AS t2 ON t2.row - 1 = t1.row

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