Sum days for multiple date ranges in one table-CodePudding

I need to count days between multiple date ranges from one table.

TABLE

ID	transdate	statuscode
1	6/24/2021	OPEN
2	11/17/2021	CLOSED
3	12/21/2021	OPEN

Days between first OPEN and CLOSED = 146 Days between second OPEN and @Today = 30 For a total of 176.

And there could be multiple Open & Closed sets.

CodePudding user response：

Try following Code:

declare @MyTbl table
(
ID int, transdate date, statuscode nvarchar(50)
)

insert into @MyTbl (ID, transdate, statuscode) values (1,CAST(N'6/24/2021' as Date), N'OPEN')
insert into @MyTbl (ID, transdate, statuscode) values (2,CAST(N'11/17/2021' as Date), N'CLOSED')
insert into @MyTbl (ID, transdate, statuscode) values (3,CAST(N'12/21/2021' as Date), N'OPEN')


select  Sum(DATEDIFF(DAY, D.transdate , D.NextDate)) as SumOfDiffDates from (
select ID, transdate, statuscode , 
case when statuscode = N'OPEN' and (ID <> (select MAX(ID) as MaxIDs from @MyTbl)) 
     then (LEAD(transdate) over (order by ID)) 
     else  (case when statuscode = N'OPEN' and (ID = (select MAX(ID) as MaxIDs from @MyTbl)) then cast(getdate() as date) else null end) 
     end as NextDate from @MyTbl
) as D where D.NextDate is not null

This query can be used for any number of records. I wrote the bet dynamically so that any number of records entered would give the correct answer.