Finding the Out of Stock days and the disabled number of days at an item level

Transact-SQL
1

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...Can you please help here!..

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.360','1','2'),
('4213','2019-06-05 11:56:41.360','2','3'),
('4213','2019-06-07 13:56:41.360','3','1'),
('5214','2019-06-01 09:56:41.360','3','1'),
('5214','2019-06-02 09:56:41.360','1','2'),
('5214','2019-06-05 09:56:41.360','2','1'),
('7896','2019-06-02 06:26:42.717','2','1'),
('7896','2019-06-02 06:56:42.717','1','7'),
('7896','2019-06-02 08:56:42.717','7','2'),
('7896','2019-06-02 09:20:42.717','2','1'),
('7896','2019-06-03 09:20:42.717','1','3'),
('7896','2019-06-03 09:50:42.717','3','2');

--Output

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

insert into ##output values
('4213','2','2.08'),
('5214','3','0'),
('7896','0.02','0.1')

Thanks,

Arun

2

hi

as per my understanding ..

out of stock (denoted by new_status =2)
disabled (denoted by new_status >2) ..

based on this here's my query and results ..
seeing your decimals I may be missing something

please click arrow to the left for drop create data script .. 
drop table temp1 
go 

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

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

--Output
drop table output 
go 

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

insert into output values
('4213','2','2.08'),
('5214','3','0'),
('7896','0.02','0.1')
go 
select * from temp1 
select * from output

select   itemid
       , sum(case when new_status =2  then 1 else 0 end )  as outofstatusnoofdays
	   , sum(case when new_status >2  then 1 else 0 end )  as disablednoofdays 
from 
       temp1 
group by 
      itemid

image

image

image

3

@ [harishgg1]: I am finding the difference in timett in terms of days on subsequent newstatus for every itemid...

4

subsequent new status means ???
difference in timett in terms of days means ??

doing this SQL looks very very simple !!! same issues every time with Posters !!!
:+1:

5

@harishgg1: My apologies for not being detailed:
For example for itemid 4213: June 3rd 2019 11.56.42 the new status is 2 and on June 5th 2019 the status becomes 3 : so 2 days of out of stock for itemid 4213..and on June 5th the new status is disabled (3)which continues till June 7th 13.56 (anything greater than 2 is disabled) the total number of days for disabled is 2.08 for itemid 4213

6

hi arun

what about 7896 scenario .. !!! the data looks different 2 two's ..

for item id 4213 here it is .. this is SQL for one type of data scenario
.. if data starts acting funny ( diff scenarios ) then sql has to be changed

; with cte_2 as 
(
select * from temp1 where new_status = 2 and itemid = 4213
) , cte_gt_2 as 
(
select * from temp1 where new_status > 2 and itemid = 4213
) , cte_lt_2 as 
(
select * from temp1 where new_status < 2 and itemid = 4213
)
select 
         a.itemid
	  ,  datediff(ss,a.timett, b.timett)/(86400.0) as outofstatusnoofdays 
	  ,  datediff(ss,b.timett, c.timett)/(86400.0) as outofstatusnoofdays
from 
     cte_2 a 
	    join 
	cte_gt_2 b 
	         on a.itemid = b.itemid and a.timett < b.timett 
	   join 
	cte_lt_2 c 
	         on b.timett < c.timett

image

image

7 
;WITH cte_temp1 AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY itemid ORDER BY timett) AS row_num
    FROM ##temp1
)
SELECT
    t1.itemid,
    CAST(SUM(CASE WHEN t1.new_status = 2 THEN DATEDIFF(MINUTE, t1.timett, t2.timett) ELSE 0 END) / 
        (24.0*60) AS decimal(9, 2)) AS outofstatusnoofdays,
    CAST(SUM(CASE WHEN t1.new_status > 2 THEN DATEDIFF(MINUTE, t1.timett, t2.timett) ELSE 0 END) / 
        (24.0*60) AS decimal(9, 2)) AS disablednoofdays
FROM cte_temp1 t1
INNER JOIN cte_temp1 t2 ON t2.itemid = t1.itemid AND t2.row_num = t1.row_num + 1
GROUP BY t1.itemid
ORDER BY itemid

SELECT * FROM ##output ORDER BY itemid
8

Thanks Scott!!..Appreciate your help!

9

You're welcome!

10

@ScottPletcher : I seek some more guidance on the above data...A week consideration is generally from sunday to saturday ...So the above data table which I gave records only the days when there was a change in status...For instance in the above table for itemid 7896 the status changes to 2 on 3rd june 2019 and there is no record for status change after that ,so there is a understanding that the itemid 7896 remains at status 2 till 7th of june which is the entire week in consideration so those days also needed to be added as out of stock days for itemid 7896... Can you please help me with this...The same way if an item is say at old status 7 on the 4th of june then it is assumed that it was at status 7 on the 1st ,2nd and 3rd June too so those days need to be added as the disabled days for that item...can you please help me with this...