SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Calculate date difference between two rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BPSQL
Starting Member

4 Posts

Posted - 03/30/2010 :  06:38:51  Show Profile  Reply with Quote
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 - 03/30/2010 :  06:48:58  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/30/2010 :  13:50:31  Show Profile  Reply with Quote
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/

Go to Top of Page

jp1234
Starting Member

USA
3 Posts

Posted - 08/31/2010 :  16:08:43  Show Profile  Reply with Quote
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!
Go to Top of Page

rocknpop
Posting Yak Master

169 Posts

Posted - 09/01/2010 :  01:01:54  Show Profile  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/01/2010 :  04:58:33  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/01/2010 :  05:02:07  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

rocknpop
Posting Yak Master

169 Posts

Posted - 09/01/2010 :  07:47:00  Show Profile  Reply with Quote
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
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 09/01/2010 :  07:51:37  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

rocknpop
Posting Yak Master

169 Posts

Posted - 09/01/2010 :  08:20:41  Show Profile  Reply with Quote


--------------------
Rock n Roll with SQL
Go to Top of Page

jp1234
Starting Member

USA
3 Posts

Posted - 09/01/2010 :  10:55:27  Show Profile  Reply with Quote
Thanks for reply my question.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 09/01/2010 :  12:58:56  Show Profile  Reply with Quote
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


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

Go to Top of Page

jp1234
Starting Member

USA
3 Posts

Posted - 09/01/2010 :  16:16:16  Show Profile  Reply with Quote
Thanks for all of you helps. It works.
I am very appreciated of your help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000