Answered by:
Help me.

Question
-
I have a table which has 3 fields: ID, StartDate and EndDate.
My Select statement finds records based on ID and I want to Subtract StartDate of last found record from EndDate of Last-1 (Previous row of last row). But I don't know how.
Can somebody help me?
Thank you.
Sunday, December 12, 2010 8:02 AM
Answers
-
Manu
I think LEFT JOIN will more accurate ...(since he/she did not post sample data)
create table #t (c int)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
select * from #t left join #t as t1
on #t.c-1=t1.c
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/- Marked as answer by Ai-hua Qiu Tuesday, December 21, 2010 7:40 AM
Sunday, December 12, 2010 9:45 AMAnswerer
All replies
-
You need a self join.
select a.iD, a.StDate, a.EndDate, b.ID, d.StDate, b.EndDate, DATEDIFF(d,b.EndDate,a.StDate) as DtDiff from YourTable a join YourTable b on b.ID = a.ID-1
~Manu
http://sqlwithmanoj.wordpress.com- Proposed as answer by Naomi N Sunday, December 12, 2010 4:36 PM
Sunday, December 12, 2010 8:08 AM -
Manu
I think LEFT JOIN will more accurate ...(since he/she did not post sample data)
create table #t (c int)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
select * from #t left join #t as t1
on #t.c-1=t1.c
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/- Marked as answer by Ai-hua Qiu Tuesday, December 21, 2010 7:40 AM
Sunday, December 12, 2010 9:45 AMAnswerer -
Manu
I think LEFT JOIN will more accurate ...(since he/she did not post sample data)
create table #t (c int)
insert into #t values (1)
insert into #t values (2)
insert into #t values (3)
insert into #t values (4)
insert into #t values (5)
select * from #t left join #t as t1
on #t.c-1=t1.c
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
~Manu
http://sqlwithmanoj.wordpress.comSunday, December 12, 2010 10:22 AM -
WITH numbered AS ( SELECT ID, StartDate, EndDate, row_number() OVER(ORDER BY StartDate) as rowno ) SELECT b.ID, b.StartDate, b.EndDate, datediff (ss, a.EndDate, b.StartDate) FROM numbered b LEFT JOIN numbered a ON a.rowno + 1 = b.rowno ORDER BY b.ID
The solution suggested by manub22 assumes that IDs are consecutive, but that may not be a very good assumption.
If the table is big, it's better for performance to select the CTE into a temp table which you index on rowno.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online: SQL 2008, SQL 2005 and SQL 2000.
(Just click the link you need.)Sunday, December 12, 2010 4:11 PM -
Any progress?
Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAMFriday, December 17, 2010 7:54 AM