# Out of Stock days and count

• ### Question

• Hi guys,

I'm trying to find out of stock (oos) days from the inventory table FOR each product_no and location_number.

I appreciate if you can help me.

create table inventory(

transaction_date    date,

Product_number  int,

location_number int,

Quantity int,

)

insert into inventory:

2019-01-01,1,12,5

2019-01-02,1,12,5

2019-01-03,1,12,0   ->from jan 03 till jan 05 product_number 1 from location_number 12 was 3 days oos.

2019-01-04,1,12,0

2019-01-05,1,12,0

2019-01-06,1,12,1

2019-01-07,1,12,0 ->jan 07 product_number 1 from location_number 12 was 1days oos.

2019-01-08,1,12,4

2019-01-09,1,12,56

2019-01-10,1,12,0 ->from jan 10 till jan 11 product_number 1 from location_number 12 was 2 days oos.

2019-01-11,1,12,0

2019-01-12,1,12,90

2019-01-13,1,134,1

2019-01-14,1,134,23

2019-01-15,1,134,0 -> product_number 1 from location_number 134 was 2 days oos.

2019-01-16,1,134,0

2019-01-17,1,134,1

I need o calculate how many days each product_number in each location_number was oos, and finally calculate count of oos days in the end.

I need to get this result:

date,product_number,location_number,Quantity,OOs Days, count

2019-01-01,1,12,5

2019-01-02,1,12,5

2019-01-03,1,12,0,3 days,1.

2019-01-04,1,12,0

2019-01-05,1,12,0

2019-01-06,1,12,1

2019-01-07,1,12,0 ,1days,1

2019-01-08,1,12,4

2019-01-09,1,12,56

2019-01-10,1,12,0,2 days,1

2019-01-11,1,12,0

2019-01-12,1,12,90

2019-01-13,1,134,1

2019-01-14,1,134,23

2019-01-15,1,134,0,2 days,1

2019-01-16,1,134,0

2019-01-17,1,134,1

Total count=3 and Total OOS days=6 days for product_number=1 and Location_umber 12

Total count=1 and Total OOS days=2 for product_number=1 and location_umber 134

• Changed type Monday, October 14, 2019 3:53 PM
• Changed type Monday, October 14, 2019 3:57 PM
• Edited by Tuesday, October 15, 2019 12:07 AM
Monday, October 14, 2019 3:27 PM

### All replies

• Please clean up your post and create your table DDL and insert scripts.

It is hard to read your posting now.

Monday, October 14, 2019 3:57 PM
• Hi ZaraJam,

Can you Please Attach your CREATE TABLE/INSERT INTO Scripts with some sample values in readable manner and mention your expected output as well.So that it will be easy to find the solution soon.

Monday, October 14, 2019 7:53 PM
• Hi Arulmouzhi,

I did.

Thank you for your attention.

• Edited by Tuesday, October 15, 2019 12:04 AM
Tuesday, October 15, 2019 12:02 AM
• Hi Jingyang Li

I did.

Thank you for your attention.

• Edited by Tuesday, October 15, 2019 12:03 AM
Tuesday, October 15, 2019 12:02 AM
• Thanks for try,

Here is the full sample of your question.  You need to test your script before you post here.

```CREATE TABLE inventory(
transaction_date    date,
Product_number  int,
location_number int,
Quantity int
);
INSERT INTO inventory(transaction_date,Product_number,location_number,Quantity) VALUES
('2019-01-01',1,12,5)
,('2019-01-02',1,12,5)
,('2019-01-03',1,12,0)
,('2019-01-04',1,12,0)
,('2019-01-05',1,12,0)
,('2019-01-06',1,12,1)
,('2019-01-07',1,12,0)
,('2019-01-08',1,12,4)
,('2019-01-09',1,12,56)
,('2019-01-10',1,12,0)
,('2019-01-11',1,12,0)
,('2019-01-12',1,12,90)
,('2019-01-13',1,134,1)
,('2019-01-14',1,134,23)
,('2019-01-15',1,134,0)
,('2019-01-16',1,134,0)
,('2019-01-17',1,134,1);

;with mycte as (
select *, row_number()over(order by transaction_date) - row_number()over(partition by location_number,Product_number,quantity order by transaction_date) grp
from  inventory )

,mycte2 as(
Select *, sum(case when quantity=0 then 1 else null end) over(partition by Product_number,location_number, quantity,grp) cnt
,dense_rank() over( Partition by Product_number,location_number Order by Case when quantity=0 then grp else null end)  as dr
from mycte
)

select transaction_date,	Product_number,	location_number,	Quantity
,count(cnt) Over(partition by Product_number,location_number) [Total OOS days]
,max(dr) Over( Partition by Product_number,location_number) -1 [Total count]

from mycte2
order by transaction_date

drop TABLE inventory```

Tuesday, October 15, 2019 3:13 AM
• Hi Jingyang Li,

Thank you for your help and your time.

I need a bellow result for example, product_number=1,location_number=12.

transaction_date_inventory,transaction_date_OOSdays,Product_number,Location_number,Quantity,OOS days,count

2019-01-02,2019-01-05,1,12,0,3,1
2019-01-06,2019-01-07,1,12,0,1,1
2019-01-09,2019-01-11,1,12,0,2,1
2019-01-14,2019-01-16,1,12,0,2,1

transaction_date in the result should be last transaction date has quantity<>0 as transaction_date_inventory and last transaction date has quantity=0 as transaction_date_OOSdays

• Edited by Tuesday, October 15, 2019 4:02 PM
Tuesday, October 15, 2019 3:59 PM
• ```CREATE TABLE inventory(
transaction_date    date,
Product_number  int,
location_number int,
Quantity int
);
INSERT INTO inventory(transaction_date,Product_number,location_number,Quantity) VALUES
('2019-01-01',1,12,5)
,('2019-01-02',1,12,5)
,('2019-01-03',1,12,0)
,('2019-01-04',1,12,0)
,('2019-01-05',1,12,0)
,('2019-01-06',1,12,1)
,('2019-01-07',1,12,0)
,('2019-01-08',1,12,4)
,('2019-01-09',1,12,56)
,('2019-01-10',1,12,0)
,('2019-01-11',1,12,0)
,('2019-01-12',1,12,90)
,('2019-01-13',1,134,1)
,('2019-01-14',1,134,23)
,('2019-01-15',1,134,0)
,('2019-01-16',1,134,0)
,('2019-01-17',1,134,1);

;with mycte as (
select *, row_number()over(order by transaction_date) - row_number()over(partition by location_number,Product_number,quantity order by transaction_date) grp
from  inventory )

,mycte2 as(
Select *, sum(case when quantity=0 then 1 else null end) over(partition by Product_number,location_number, quantity,grp) cnt
,dense_rank() over( Partition by Product_number,location_number Order by Case when quantity=0 then grp else null end)  as dr
from mycte
)
,max(transaction_date) transaction_date_OOSdays
,Product_number,Location_number,0 as Quantity
, max(cnt) [OOS days]
,1 as [count] -- why do you need this?
from mycte2
WHERE Quantity=0

Group by Product_number,Location_number,grp
order by min(transaction_date)

drop TABLE inventory

/*
transaction_date_inventory	transaction_date_OOSdays	Product_number	Location_number	Quantity	OOS days	count
2019-01-02	2019-01-05	1	12	0	3	1
2019-01-06	2019-01-07	1	12	0	1	1
2019-01-09	2019-01-11	1	12	0	2	1
2019-01-14	2019-01-16	1	134	0	2	1

*/```

Tuesday, October 15, 2019 4:28 PM
• Thanks, Jingyang Li,

your solution is great.

It works when I put Product_number is 1 and location_number is12 in the first your query, else I couldn't get like your result.

I got this result when I won't use the filter in where clause

transaction_date_inventory,transaction_date_OOSdays,Product_Number,Location_number,Quantity,OOS days,count
2018-01-02,2018-01-03,1,12,0,1,1
2018-01-03,2018-01-04,1,12,0,1,1
2018-01-04,2018-01-05,1,12,0,1,1
2018-01-06,2018-01-07,1,12,0,1,1
2018-01-09,2018-01-10,1,12,0,1,1
2018-01-10,2018-05-11,1,12,0,1,1
2018-01-14,2018-01-15,1,134,0,1,1
2018-01-15,2018-01-16,1,134,0,1,1

zj

• Edited by Tuesday, October 15, 2019 7:26 PM
Tuesday, October 15, 2019 7:19 PM
• Please post your whole sample data set in script so I can look at it. You can modify my previous sample. Thanks.

```,('2019-01-13',1,134,1)
,('2019-01-14',1,134,23)
,('2019-01-15',1,134,0)
,('2019-01-16',1,134,0)
,('2019-01-17',1,134,1);```

returns:

`2019-01-14	2019-01-16	1	134	0	2	1`

Tuesday, October 15, 2019 7:53 PM
• Thank you so much Jingyang Li for your attention.

with below data set query doesn't work which means don't give me the below result), Could you please help me with this data set. ( we also have duplicated data in the data set)

```INSERT INTO inventory(transaction_date,Product_number,location_number,Quantity) VALUES
('2018-12-27',1,12,5) ,('2019-01-01',1,12,0)
,('2019-01-02',1,12,0),('2019-01-02',1,45,560),('2019-01-03',1,12,5)
,('2019-01-03',1,12,5)
,('2019-01-04',1,12,0)
,('2019-01-05',1,45,0)
,('2019-01-06',1,12,1)
,('2019-01-07',1,12,0),('2019-01-07',8,12,23),('2019-01-08',1,12,0)
,('2019-01-09',1,12,4)
,('2019-01-09',1,12,56)
,('2019-01-10',1,12,0)
,('2019-01-11',1,12,0)
,('2019-01-12',1,12,90)
,('2019-01-13',1,134,1)
,('2019-01-14',1,45,23),('2019-01-14',1,45,23)
,('2019-01-15',1,134,0)
,('2019-01-16',1,134,0)
,('2019-01-17',8,12,0),('2019-01-20',1,45,0),('2019-01-22',1,45,0);and I need the below result:transaction_date_inventory,transaction_date_OOSdays,Product_Number,Location_number,Quantity,OOS days,count2018-12-27,2019-01-02,1,12,0,2,12019-01-03,2019-01-08,1,12,0,5,12019-01-09,2019-01-11,1,12,0,2,12019-01-07,2019-01-17,8,12,10,12019-01-14,2019-01-22,1,45,0,8,12019-01-13,2019-01-16,1,134,3,1```

<g class="gr_ gr_7 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="7" id="7">zj</g>

• Edited by Thursday, October 17, 2019 12:34 PM
Thursday, October 17, 2019 2:23 AM
• From your result you posted for your sample data, it is not consistent with your question.

Can you check it and see whether you can use the same logic from your original description?  Thanks.

```CREATE TABLE inventory(
transaction_date    date,
Product_number  int,
location_number int,
Quantity int
);
INSERT INTO inventory(transaction_date,Product_number,location_number,Quantity) VALUES
('2018-12-27',1,12,5)
,('2019-01-01',1,12,0)
,('2019-01-02',1,12,0)
,('2019-01-02',1,45,560)
,('2019-01-03',1,12,5)
,('2019-01-03',1,12,5)
,('2019-01-04',1,12,0)
,('2019-01-05',1,45,0)
,('2019-01-06',1,12,1)
,('2019-01-07',1,12,0)
,('2019-01-07',8,12,23)
,('2019-01-08',1,12,0)
,('2019-01-09',1,12,4)
,('2019-01-09',1,12,56)
,('2019-01-10',1,12,0)
,('2019-01-11',1,12,0)
,('2019-01-12',1,12,90)
,('2019-01-13',1,134,1)
,('2019-01-14',1,45,23)
,('2019-01-14',1,45,23)
,('2019-01-15',1,134,0)
,('2019-01-16',1,134,0)
,('2019-01-17',8,12,0)
,('2019-01-20',1,45,0)
,('2019-01-22',1,45,0);

;with mycte as (
select *, row_number()over(partition by location_number,Product_number  order by transaction_date) - row_number()over(partition by location_number,Product_number,quantity  order by transaction_date) grp
from  inventory )

,mycte2 as(
Select *, sum(case when quantity=0 then 1 else null end) over(partition by Product_number,location_number, quantity,grp) cnt
,dense_rank() over( Partition by Product_number,location_number Order by Case when quantity=0 then grp else null end)  as dr
from mycte
)
,max(transaction_date) transaction_date_OOSdays
,Product_number,Location_number,0 as Quantity
, max(cnt) [OOS days]
,1 as [count] -- why do you need this?
from mycte2
WHERE Quantity=0

Group by Product_number,Location_number,grp
order by Location_number,Product_Number, min(transaction_date)

--select * from mycte2
--order by Product_number,Location_number,transaction_date
drop TABLE inventory

/*
transaction_date_inventory	transaction_date_OOSdays	Product_number	Location_number	Quantity	OOS days	count
2018-12-31	2019-01-02	1	12	0	2	1
2019-01-03	2019-01-04	1	12	0	1	1
2019-01-06	2019-01-08	1	12	0	2	1
2019-01-09	2019-01-11	1	12	0	2	1
2019-01-16	2019-01-17	8	12	0	1	1
2019-01-04	2019-01-05	1	45	0	1	1
2019-01-19	2019-01-22	1	45	0	2	1
2019-01-14	2019-01-16	1	134	0	2	1
*/```

Friday, October 18, 2019 2:49 PM
• Thank you so much Jingyang Li, It works now.

Again I appreciate your help.

zj

Tuesday, October 22, 2019 3:24 AM