Calculate date difference between two 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)

Calculate date difference between two rows

Author

Topic

BPSQL
Starting Member

4 Posts

Posted - 2010-03-30 : 06:38:51

Hi all,

I have been struggling to find a solution to this problem and was wondering if anyone can help. Is there any way to calculate the date difference between two records where the records are in the same group. eg:

ID Group StartDate EndDate
1 1 26/04/08 10/05/08
2 1 02/06/08 09/07/08
3 2 20/07/09 21/07/09
4 2 21/07/09 28/07/09
5 2 28/07/09 12/08/09

So I need to find the difference between the startdate and enddate for the same group
Difference between ID1 Enddate(10/05/08) and ID2 StartDate(02/06/08)
Difference between ID3 Enddate(21/07/09) and ID4 StartDate(21/07/09)
Difference between ID4 Enddate(28/07/09) and ID5 StartDate(28/07/09)

Any help on this matter would be greatly appreciated.

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-03-30 : 06:48:58

Might this be what you are looking for?

Select DateDiff(day, T.EndDate, T2.StartDate)
FROM Table T1
Outer Apply (Select TOP 1 T2.StartDate FROM Table T2 WHERE T2.Group = T1.Group AND T2.ID <> T1.ID ORDER BY ID)

If there is always another record you could presume this and use Cross Apply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:50:31

it should be


Select DateDiff(day, f.EndDate, T.StartDate) 
FROM Table T
Outer Apply (Select TOP 1 T2.EndDate FROM Table T2 WHERE T2.Group = T.Group AND T2.ID < T.ID ORDER BY T2.ID DESC)f

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jp1234
Starting Member

3 Posts

Posted - 2010-08-31 : 16:08:43

Hi, Visakh16 and Buzzard724,

I love your post, it perfectly works in SQL 2005.

I have the similar quesiton by using SQL 2000. Since Outer Apply doesn't work in SQL 2000, would you please help to resolve the similar problem by using SQL 2000?

Thansk a lot!

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-09-01 : 01:01:54

Hi, can you try this:

SELECT DATEDIFF(dd,t3.startDate,t2.endDate),t2.grp
FROM t1 t2 INNER JOIN t1 t3 ON t3.grp=t2.grp AND t2.id+1=t3.id
GROUP BY t3.startDate,t2.endDate,t2.grp
ORDER BY t2.grp

--------------------
Rock n Roll with SQL

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 04:58:33

rocknpop -- that's not safe -- if ID is an autonumber column (and it probably is) then there is no guarantee of concurrency. You will get gaps (from deletions / rollbacks during inserts etc)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 05:02:07

jp1234 -- Re sql 2000.

There isn't really a *nice* way of doing this on sql 2000. Probably the least awkward way is to make a temp table with an autonumber ID (IDENTITY (1, 1)) and insert into the temp table (in order) all the rows you want to do this to.

Once you have the temp table (with it's nice ordered sequence autonumber column) you can do as rocknpop suggested above.

You could use a 'quirky update' but they are horrible. Just google quirky update if you want to learn more.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-09-01 : 07:47:00

Hi Charlie, yes I do agree it will fail if ID is not in a sequence. The temp table approach as you suggested should be the way to go.

Thanks

--------------------
Rock n Roll with SQL

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 07:51:37

Hi RocknPop -- to be fair it would have worked for OP's sample data!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-09-01 : 08:20:41

--------------------
Rock n Roll with SQL

jp1234
Starting Member

3 Posts

Posted - 2010-09-01 : 10:55:27

Thanks for reply my question.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 12:58:56

[code]Select DateDiff(day, (Select TOP 1 T2.EndDate FROM Table T2 WHERE T2.Group = T.Group AND T2.ID < T.ID ORDER BY T2.ID DESC), T.StartDate)
FROM Table T
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jp1234
Starting Member

3 Posts

Posted - 2010-09-01 : 16:16:16

Thanks for all of you helps. It works.
I am very appreciated of your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources