Calculate time span between two specific statuses on the database for each ID-CodePudding

I have a table on the database that contains statuses updated on each vehicle I have, I want to calculate how many days each vehicle spends time between two specific statuses 'Maintenance' and 'Read'.

My table looks something like this

and I want to result to be like this, only show the number of days a vehicle spends in maintenance before becoming ready on a specific day

The code I written looks like this

drop table if exists #temps1

select

 VehicleId,

 json_value(VehiclesHistoryStatusID.text,'$.en') as VehiclesHistoryStatus,

VehiclesHistory.CreationTime,

datediff(day, VehiclesHistory.CreationTime , 
lead(VehiclesHistory.CreationTime ) over (order by VehiclesHistory.CreationTime ) ) as days,

lag(json_value(VehiclesHistoryStatusID.text,'$.en')) over (order by VehiclesHistory.CreationTime) as PrevStatus,

case 

when   (lag(json_value(VehiclesHistoryStatusID.text,'$.en')) over (order by VehiclesHistory.CreationTime) <> json_value(VehiclesHistoryStatusID.text,'$.en')) THEN datediff(day, VehiclesHistory.CreationTime , (lag(VehiclesHistory.CreationTime ) over (order by VehiclesHistory.CreationTime ))) else 0 end as testing


into #temps1

from fleet.VehicleHistory VehiclesHistory
left join Fleet.Lookups as VehiclesHistoryStatusID on VehiclesHistoryStatusID.Id = VehiclesHistory.StatusId


where (year(VehiclesHistory.CreationTime) > 2021 and (VehiclesHistory.StatusId = 140 Or VehiclesHistory.StatusId = 144) )

group by VehiclesHistory.VehicleId ,VehiclesHistory.CreationTime , VehiclesHistoryStatusID.text 

order by  VehicleId desc

drop table if exists #temps2

select * into #temps2  from #temps1 where testing <> 0

select * from #temps2

CodePudding user response：

Try this

SELECT innerQ.VehichleID,innerQ.CreationDate,innerQ.Status
,SUM(DATEDIFF(DAY,innerQ.PrevMaintenance,innerQ.CreationDate)) AS DayDuration

FROM
(
SELECT t1.VehichleID,t1.CreationDate,t1.Status,
(SELECT top(1) t2.CreationDate FROM dbo.Test t2 
    WHERE t1.VehichleID=t2.VehichleID
    AND t2.CreationDate<t1.CreationDate
    AND t2.Status='Maintenance'
    ORDER BY t2.CreationDate Desc) AS PrevMaintenance
FROM
dbo.Test t1 WHERE t1.Status='Ready'
) innerQ
WHERE innerQ.PrevMaintenance IS NOT NULL 
GROUP BY innerQ.VehichleID,innerQ.CreationDate,innerQ.Status

In this query first we are finding the most recent 'maintenance' date before each 'ready' date in the inner most query (if exists). Then calculate the time span with DATEDIFF and sum all this spans for each vehicle.