Sales Order vs Warehouse Inventory Stock FIFO

• weilingleong

  SSC Veteran

  Points: 205

  More actions

  September 30, 2017 at 1:08 am

  #323902

  Dear All,

  Table  1 is the sales order that received in each different day for inventory ID 1225 and 1496.  Table 2 is the warehouse store that available for each document. 
  

  If  we would like to get the result as below, allocated table 2 quantity to table 1 without using LOOP, can it be done?

  

  Hope my english is underestandable.  Thank you
  Ling

• TheCTEGuy

  Ten Centuries

  Points: 1032

  More actions

  September 30, 2017 at 4:26 am

  #1961768

  weilingleong - Saturday, September 30, 2017 1:08 AM

  Dear All,

  Table  1 is the sales order that received in each different day for inventory ID 1225 and 1496.  Table 2 is the warehouse store that available for each document. 
  

  If  we would like to get the result as below, allocated table 2 quantity to table 1 without using LOOP, can it be done?

  

  Hope my english is underestandable.  Thank you
  Ling

  Trying to answer this.
  As per my understanding below is the sol. But in the result i could not understand that from where are you getting NULL's in allocated quantity?
  Since there is no NULL in Table 2 that u have provided.
  
use SSCForums

  create table inventory(invid int,trandate datetime,document varchar(10),quantity int)
go
create table warehouse(invid int,createddate datetime,name varchar(10),quantity int)
go
insert into inventory
select 1225,'2017-10-14','xyz',800
union all
select 1225,'2017-10-17','xyza',1000
union all
select 1496,'2017-10-13','xyzb',200
union all
select 1496,'2017-10-12','xyzc',50
union all
select 1496,'2017-10-19','xyzd',1000
union all
select 1496,'2017-10-11','xyze',500
union all
select 1496,'2017-10-05','xyzf',800
go

  insert into warehouse
select 1225,'2017-10-14','xyz',800
union all
select 1225,'2017-10-17','xyza',500
union all
select 1496,'2017-10-13','xyzb',100
union all
select 1496,'2017-10-12','xyzc',20
union all
select 1496,'2017-10-19','xyzd',400
union all
select 1496,'2017-10-11','xyze',100
union all
select 1496,'2017-10-05','xyzf',600
go

  SELECT A.INVID,A.trandate,A.document,A.quantity,A.quantity - B.quantity AS [REMAINING_QTY],B.quantity AS [ALLOCATED QUANITY]
FROM inventory A
INNER JOIN warehouse B
ON A.invid = B.invid

  First solve the problem then write the code !

• weilingleong

  SSC Veteran

  Points: 205

  More actions

  September 30, 2017 at 9:56 pm

  #1961788

  The NULL is because the warehouse dont have the stock for allocation.

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  October 3, 2017 at 12:47 pm

  #1962129

  Look at the following article which talks about using Windowed Functions to solve FIFO queues. FIFO Queues

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA