Hi @Hellothere8028 ,
My output is similar with your output but the value of disablednoofdays of itemid 7896 is 0.10 from my side.
I checked manually, (status 3(0.5 hour)+status 7(2 hours))/24 hours=0.10. Please help doublecheck from your side.
Please also notice the data format of timett in ##temp1.
Please refer below query and check whether it is working:
create table ##temp1
(itemid varchar(30),
timett datetime2,
old_status int,
new_status int)
insert into ##temp1 values
('4213','2019-06-03 11:56:41','2','1'),
('4213','2019-06-05 11:56:41','1','3'),
('4213','2019-06-07 13:56:41','3','2'),
('5214','2019-06-02 09:56:41','3','1'),
('5214','2019-06-03 09:56:41','1','2'),
('5214','2019-06-05 09:56:41','2','3'),
('7896','2019-06-02 06:26:43','2','1'),
('7896','2019-06-02 06:56:43','1','7'),
('7896','2019-06-02 08:56:43','7','2'),
('7896','2019-06-02 09:20:43','2','1'),
('7896','2019-06-03 09:20:43','1','3'),
('7896','2019-06-03 09:50:43','3','2')
;With cte As
(Select itemid, old_status, new_status, timett,
Lag(timett) Over(Partition By itemid Order By timett) As PriorTime,
lead(timett) Over(Partition By itemid Order By timett) As BehindTime
From ##temp1)
,cte1 as(
select itemid, old_status, new_status, timett,
case when PriorTime is null then DATEADD(week, DATEDIFF(week, -1, timett), -1) else PriorTime end PriorTime,
case when BehindTime is null then DATEADD(week, DATEDIFF(week, -1, timett), 5) end BehindTime
from cte)
Select itemid,
Cast(Cast(Sum(Case When old_status = 2 Then DateDiff(second, PriorTime, timett) Else 0 End
+ Case When new_status = 2 Then DateDiff(second, timett, BehindTime) Else 0 End) As Float)
/86400 As decimal(7,2)) As outofstatusnoofdays,
Cast(Cast(Sum(Case When old_status > 2 Then DateDiff(second, PriorTime, timett) Else 0 End
+ Case When new_status > 2 Then DateDiff(second, timett, BehindTime) Else 0 End) As Float)
/86400 As decimal(7,2)) As disablednoofdays
From cte1
Group By itemid;
Output:
If the response is helpful, please click "Accept Answer" and upvote it.
Best regards
Melissa