SQLTeam.com | Weblogs | Forums

Getting the running/adjusted inventory


#1

Dear sirs,

I would like to humbly request for your help to obtain the running inventory of items

I have tables like:
Tblbeginning inventory
Itemcode
Qty
Date

Tbl inventory adjustment
Itemcode
Qty
Date

Tblsales
Itemcode
Qty
Date

Tblpurchases
Itemcode
Qty
Date

I want that, when record in tbl inventory adjustment has the date greater than the Other 3 tables, the Running inventory to be displayed in resultset will be the qty in inventory adjustment.

Thank you in advance for anyone who wil help on this please.


#2

declare @Tblbeginning_inventory table (Itemcode int, Qty int, [Date] datetime)
declare @Tbl_inventory_adjustment table (Itemcode int, Qty int, [Date] datetime)
declare @Tblsales table (Itemcode int, Qty int, [Date] datetime)
declare @Tblpurchases table (Itemcode int, Qty int, [Date] datetime)

insert into @Tblbeginning_inventory (Itemcode,Qty,[Date]) values
(101,1,'20160101'),
(102,2,'20160115'),
(103,3,'20160118'),
(104,4,'20160131')

insert into @Tbl_inventory_adjustment (Itemcode,Qty,[Date])values
(101,10,'20160722'),
(102,20,'20160718'),
(103,30,'20160721'),
(104,40,'20160701')

insert into @Tblsales (Itemcode,Qty,[Date])values
(101,1,'20160101'),
(102,2,'20160115'),
(103,3,'20160118'),
(104,4,'20160131')

insert into @Tblpurchases (Itemcode,Qty,[Date])values
(101,1,'20160101'),
(102,2,'20160115'),
(103,3,'20160118'),
(104,4,'20160131')

--Select * from @Tblbeginning_inventory
--Select * from @Tbl_inventory_adjustment
--Select * from @Tblsales
--Select * from @Tblpurchases

Select
case when (adj.[Date] > beg.[Date] or adj.[Date] > sal.[Date] or adj.[Date] > pur.[Date]) then adj.Qty end as [Running inventory]

from @Tbl_inventory_adjustment adj
inner join @Tblbeginning_inventory beg on adj.Itemcode = beg.Itemcode
inner join @Tblsales sal on sal.Itemcode = beg.Itemcode
inner join @Tblpurchases pur on pur.Itemcode = beg.Itemcode