CODEWITHSUNDEEP
sqlsql-servert-sql
Vyacheslav Shcherbakov
Vyacheslav Shcherbakov

Reputation: 55

SQL Server : get days between dates from same column

Let's say I have a table with the following dates:

2017-09-07
2017-09-07
2017-09-07
2017-09-07
2017-09-07
2017-09-07
2015-09-09
2014-09-13
2014-09-13
2014-09-13
2014-09-13
2012-09-11
2012-09-11
2012-09-07
2012-09-07

How can I get the days difference between date changes (how many days passed from a date change)

I should get this:

DATE        DAYSPASSED
----------------------
2017-09-07  729
2017-09-07  729
2017-09-07  729
2017-09-07  729
2017-09-07  729
2017-09-07  729
2015-09-09  361
2014-09-13  732
2014-09-13  732
2014-09-13  732
2014-09-13  732
2012-09-11    4
2012-09-11    4
2012-09-07    0
2012-09-07    0

Upvotes: 1

Views: 208

Answers (2)

Horaciux
Horaciux

Reputation: 6487

declare @Table1 table
    ([dates] datetime)
;

INSERT INTO @Table1
    ([dates])
VALUES
    ('2017-09-07 00:00:00'),
    ('2017-09-07 00:00:00'),
    ('2017-09-07 00:00:00'),
    ('2017-09-07 00:00:00'),
    ('2017-09-07 00:00:00'),
    ('2017-09-07 00:00:00'),
    ('2015-09-09 00:00:00'),
    ('2014-09-13 00:00:00'),
    ('2014-09-13 00:00:00'),
    ('2014-09-13 00:00:00'),
    ('2014-09-13 00:00:00'),
    ('2012-09-11 00:00:00'),
    ('2012-09-11 00:00:00'),
    ('2012-09-07 00:00:00'),
    ('2012-09-07 00:00:00')
;
select a.dates, b.dayspassed from
@table1 a inner join ( 

select   dates, 
coalesce(datediff(day,LEAD (dates) OVER (order by  dates desc),dates),0) dayspassed

 from @table1  
  group by dates) b on a.dates=b.dates
  order by a.dates desc

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 82020

Perhaps with a CROSS APPLY

Example

Select A.*
      ,DaysPassed = IsNull(DateDiff(DAY,B.NxtValue,A.DateCol),0)
 From  YourTable A
 Cross Apply (Select NxtValue=max(DateCol) From @YourTable Where DateCol<A.DateCol) B
 Order By DateCol Desc

Returns

enter image description here

Upvotes: 2

Related Questions