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..
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')