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. 

Answer 1

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

Answer 2

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/

Answer 3

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/

Right Uri, should be a self LEFT JOIN.

---

~Manu
http://sqlwithmanoj.wordpress.com

Answer 4

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, [e-mail]
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)

Answer 5

Any progress?

---

Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM