CODEWITHSUNDEEP
sqlsql-server-2008
nereide
nereide

Reputation: 152

Difference between two consecutive dates, stored in separate rows

Each line of my table contains a reference and a date. I am trying to find the number of days between one date and the previous one.

Let's work on the example below:

CREATE TABLE #Test
(   
    Ref NVARCHAR(100) NOT NULL,
    Dat DATETIME NOT NULL
)

INSERT INTO #Test ( Ref, Dat )
VALUES  ( N'ABC123', GETDATE() ), 
        ( N'DHI458', GETDATE() + 1 ),
        ( N'HGYU556', GETDATE() - 3 ),
        ( N'UUU021', GETDATE() + 17 )

I am trying to get something like :

UUU021  2017-01-01 17:27:00.720  17 
DHI458  2016-12-16 17:27:00.720  1
ABC123  2016-12-15 17:27:00.720  3
HGYU556 2016-12-12 17:27:00.720  NULL

in a single select. Is it possible? I'm working with SQL Server 2008.

Upvotes: 0

Views: 222

Answers (3)

Anand
Anand

Reputation: 1123

I generally like to self-join until you have effectively 2 tables that are offset by 1 row:

select t1.Ref, t1.Dat. datediff(dd, t1.Dat, t2.Dat) as diff
from #Test as t1
inner join #Test as t2
on t2.Dat > t1.Dat
and not exists (
    select 1
    from #Test as t3
    where t3.Dat > t1.Dat
    and t3.Dat < t2.Dat
    )
order by t1.Dat desc

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

Gordon's approach is solid, but here's another

with CTE as
(
select Ref, Dat, row_number() over (order by Dat desc) as DatO
from #Test
)
select A1.Ref, A1.Dat, A1.Dat-A2.Dat as Diff
from CTE A1
left join CTE A2
    on A1.DatO = A2.DatO - 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

SQL Server 2012+ supports lag(). In SQL Server 2008, you have several options -- none really good. I would go with outer apply:

select t.*,
       datediff(day, tprev.date, t.date) as diff
from #test t outer apply
     (select top 1 t2.*
      from #test t2
      where t2.date < t.date
      order by t2.date desc
     ) tprev;

Upvotes: 2

Related Questions