Finding the Number of Out of Stock Days and Disabled days considering the start and end of the week

Question

Hi All,

Hope you are doing well...I am trying to find the number of days an item was out of stock (denoted by new_status =2) and the number of days the item was disabled (denoted by new_status >2) .. New_status =1 denotes that the item is available)... the number of days can be denoted as a decimal too(like 2.2 days ,3.4 days).. The new_status denotes the current status and the old_Status denotes the prior status... I have also provided the output table too... The week for consideration is 2nd June to 8th June....In the table below the item 4213 has a record only on June 3rd with the old status as 2(outofstock)-so the implied meaning the item had status 2 on 2nd June too ..So when the number of days for out of stock is calculated for 4213 June 2nd should also be taken into consideration...Similarly for item 7896 on june 3rd the new status becomes 2 ;so this implies till june 8th the itemid 7896 would have had the status as 2 outofstock only...So the days after 3rd till 8th also needs to be taken into consideration for calculating the out of stock days for item 7896..Can you please help here..

@Tom Cooper : I had to create a separate post as the forum dint allow me to provide all this info in the reply...

Previous post reference:Finding the Out of Stock days and the disabled number of days at an item level

create table ##temp1
(itemid varchar(30),
timett datetime2,
old_status int,
new_status int)

insert into ##temp1 values
('4213','03-06-2019 11:56:41','2','1'),
('4213','05-06-2019 11:56:41','1','3'),
('4213','07-06-2019 13:56:41','3','2'),
('5214','02-06-2019 09:56:41','3','1'),
('5214','03-06-2019 09:56:41','1','2'),
('5214','05-06-2019 09:56:41','2','3'),
('7896','02-06-2019 06:26:43','2','1'),
('7896','02-06-2019 06:56:43','1','7'),
('7896','02-06-2019 08:56:43','7','2'),
('7896','02-06-2019 09:20:43','2','1'),
('7896','03-06-2019 09:20:43','1','3'),
('7896','03-06-2019 09:50:43','3','2')

create table ##output
(itemid varchar(20),
outofstatusnoofdays float,
disablednoofdays float)

insert into ##output values
('4213','1.91','2.08'),
('5214','2','2.99'),
('7896','4.87','0.04')

Answer 1

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