Sales Order vs Warehouse Inventory Stock FIFO

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheCTEGuy SSC Eights! Points: 922 More actions · Answer 1

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