SQL-Code

1

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(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

2

hi i am trying to understand what you are wanting

NOT able to ...

please click arrow to the left for drop create data 
drop table inventory
go 

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);

select 'data',* from inventory
go 

drop table output
go 

create table output 
(
transaction_date_inventory date,
transaction_date_OOSdays date,
Product_Number int,
Location_number int,
Quantity int,
[OOS days] int,
[count] int 
)
go 


insert into output select '2018-12-27','2019-01-02',1,12,0,2,1

insert into output select '2019-01-03','2019-01-08',1,12,0,5,1

insert into output select '2019-01-09','2019-01-11',1,12,0,2,1

insert into output select '2019-01-07','2019-01-17',8,12,0,10,1

insert into output select '2019-01-14','2019-01-22',1,45,0,8,1

insert into output select '2019-01-13','2019-01-16',1,134,0,3,1
go 

select 'output wanted',* from output 
go 

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

image
image.png1865×1574 631 KB