SQL Server : get days between dates from same column

Question

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

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

John Cappelletti · Accepted Answer · 2017-09-08 00:52:13Z

2

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

answered Sep 8, 2017 at 0:52

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Horaciux · Accepted Answer · 2017-09-08 01:02:01Z

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

Collectives™ on Stack Overflow

SQL Server : get days between dates from same column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related