From the below, is there any way to calculate the number of days when the stock was out of stock. 1 in column stock shows when it was out of stock, when it changes to 0 means the stock came back in, so there is a difference of 3 days between 08/04/2022 to 11/04/2022. Not sure if this is possible.

CodePudding user response：

Not fully tested, and not 100% i got the question :) But, i'd try something with LAG or LEAD, something like this.

I hope this is a good point in the right direction for you. Have a good eve.

select distinct 
t.itemcode,t.date_time,t.stock ,
datediff(d, lead(t.date_time) over (order by date_time desc),t.date_time) as DAYS_OOS
from 
tblTestabc as t
where t.stock=0
order by t.date_time desc

CodePudding user response：

We can use lag to get the previous date and Datediff to find the difference.
I've used group by ordercode and stock so that it will work with multiple items and show both figures. If we only want stock 0 then add where stock = 0 and remove stock from the select and group by.

create table stock(ordercode int,date_time date,stock int);
insert into stock values(1,'2022-04-01',1),(1,'2022-04-04',0),(1,'2022-04-06',1),(1,'2022-04-10',0),    (1,'2022-04-15',1);

with cte as
(select
*,
datediff(d,lag(date_time)over
  (partition by ordercode
  order by date_time),date_time) days
  from stock
)
SELECT
  ordercode,
  stock,
  sum(COALESCE(days,0)) days
from cte
group by
  ordercode,
  stock
order by
  ordercode;
GO

ordercode | stock | days
--------: | ----: | ---:
        1 |     0 |    7
        1 |     1 |    7

db<>fiddle here