locked
Help me. RRS feed

  • 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 AM
    Answerer

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 AM
    Answerer
  • 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
    Sunday, 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 SLAM
    Friday, December 17, 2010 7:54 AM