none
Out of Stock days and count RRS feed

  • 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 ZaraJ Monday, October 14, 2019 3:53 PM
    • Changed type Jingyang LiModerator Monday, October 14, 2019 3:57 PM
    • Edited by ZaraJ 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
    Moderator
  • 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 ZaraJ 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 ZaraJ 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
    Moderator
  • 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 ZaraJ 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
    )
    Select Dateadd(day,-1,min(transaction_date)) transaction_date_inventory 
    ,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
    Moderator
  • 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 ZaraJ 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
    Moderator
  • 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,count

    2018-12-27,2019-01-02,1,12,0,2,1

    2019-01-03,2019-01-08,1,12,0,5,1

    2019-01-09,2019-01-11,1,12,0,2,1

    2019-01-07,2019-01-17,8,12,10,1

    2019-01-14,2019-01-22,1,45,0,8,1

    2019-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 ZaraJ 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
    )
    Select Dateadd(day,-1,min(transaction_date)) transaction_date_inventory 
    ,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
    Moderator
  • Thank you so much Jingyang Li, It works now. 

    Again I appreciate your help. 


    zj

    Tuesday, October 22, 2019 3:24 AM