Calculate days based on date range

user_12073359 0
Viewed 66

I have data like below

create table #Temp(Id int, FromDate date, ToDate date)
Insert into #Temp
values(1,'9/1/2019','9/1/2019'),
        (2,'9/2/2019','9/3/2019'),
        (3,'9/2/2019','9/3/2019'),
        (4,'9/4/2019','9/6/2019'),
        (5,'9/7/2019','9/7/2019')

I am trying to calculate the difference and create days i.e Day 1, Day 2-3 etc...

Expected result 

Id  FromDate    ToDate      Display
1   01/09/2019  01/09/2019  Day 1
2   02/09/2019  03/09/2019  Day 2-3
3   02/09/2019  03/09/2019  Day 2-3
4   04/09/2019  06/09/2019  Day 4-6
5   07/09/2019  07/09/2019  Day 7

I have tried below code using datediff, but not sure how to relate to previous row and get the date range

select *, DATEDIFF(DAY,FromDate,ToDate)
from #Temp
sql-server-2016 sql-server sql
3 Answers

Use first_value

select *
 , datediff(day, first_value(FromDate) over(order by FromDate), FromDate) + 1
 , datediff(day, first_value(FromDate) over(order by FromDate), ToDate) + 1
from #Temp
user_62199790

You can try this if you want exactly the same output

Select 
      * ,
     case 
      when (FromDate != ToDate) 
       then 
        'Day '+ DATEPART(Day,FromDate) + '-' + DATEPART(Day,ToDate)
       else
      'Day '+ DATEPART(Day,FromDate)
      END AS Display
    From #Temp
user_97173650

You don't want the previous row's value, you want the earliest fromdate, and then you can compare it to every row.

select id, min (fromdate) over (order by fromdate asc) as earliest_date,fromDate,todate,
datediff(day,min (fromdate) over (order by fromdate asc),fromdate)+1,
datediff(day,min (fromdate) over (order by fromdate asc),todate)+1
from
#temp

Fiddle

user_27515730
Related