Inventory calculation with running balance

Question

Hi Forumer's,

I have 3 tables to joined. my objective is to create an invetory view with running balance.

I could not figure out to work on this kind of approach. need your help guys. sample data is give.

your reply is very much appreciated. thanks.

Create table #PurcLine
(Purchid nvarchar(35),
Itemid nvarchar(35),
DeliveryDate datetime,
QtyOrdered int,
PurchPrice numeric(28,12),
Inventdimid nvarchar(35))
Insert into #Purcline(purchid,itemid,DeliveryDate,qtyOrdered,purchprice,Inventdimid) values('PH0057151','HTC1534','2012/06/30',4900,9,'DIM0000656')
Insert into #Purcline(purchid,itemid,DeliveryDate,qtyOrdered,purchprice,Inventdimid) values('PH0058374','HTC1534','2012/07/09',-776,9,'DIM4929678')
Insert into #Purcline(purchid,itemid,DeliveryDate,qtyOrdered,purchprice,Inventdimid) values('PH0058212','HTC1534','2012/07/10',384,9,'DIM0000656')
Create table #PurcTable
(Purchid nvarchar(35),
VendRef nvarchar(50),
Purchpoolid nvarchar(2))
Insert into #PurcTable(Purchid,VendRef, Purchpoolid) values ('PH0057151','CM00197','PR')
Insert into #PurcTable(Purchid,VendRef, Purchpoolid) values ('PH0058374','CM00197','PR')
Insert into #PurcTable(Purchid,VendRef, Purchpoolid) values ('PH0058212','July additional (Carry over)','RP')
Create table #Inventran
(Itemid nvarchar(35),
Transrefid nvarchar(35),
datephysical datetime,
Qty int)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-04','PH0057151',700)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-05','PH0057151',250)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-06','PH0057151',100)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-09','PH0057151',34)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-14','PH0057151',500)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-16','PH0057151',500)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-18','PH0057151',1615)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-20','PH0057151',320)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-21','PH0057151',500)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-06-22','PH0057151',381)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-07-11','PH0058374',-776)
Insert into #Inventran(Itemid,datephysical,Transrefid,Qty) values('HTC1534','2012-07-14','PH0058212',384)
SAMPLE RESULT
---------------------------------------------------------------------------------------------------------------
Purchid----Itemid---deliverydate--datephysical--QtyOrdered--QtyReceived--OpenQty--Purchprice--Purchpoolid--VendRef
---------------------------------------------------------------------------------------------------------------
PH0057151--HTC1534--2012/06/30----2012-06-04-----4900-------700----------4200-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-05-----4200-------250----------3950-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-06-----3950-------100----------3850-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-09-----3850-------34 ----------3816-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-14-----3816-------500----------3316-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-16-----3316-------500----------2816-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-18-----2816-------1615---------1201-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-20-----1201 ------320----------881 -----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-21-----881--------500-----------381-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-22-----381--------381------------0------9.00-----------PR-------CM00197
PH0058374--HTC1534--2012/07/09----2012-07-11---- -776------ -776  ---------0------9.00-----------PR-------CM00197
PH0058212--HTC1534--2012/07/10----2012-07-14---- 384------- 384  ----------0------9.00-----------RP------July additional (Carry over)

Answer 1

here you have sample code

SELECT pr.purchid,iv.itemid,pr.DeliveryDate,iv.datephysical,pr.qtyOrdered,iv.Qty,pr.qtyOrdered-iv.Qty,pr.purchprice  AS[OpenQty] 
,pt.Purchpoolid,pt.VendRef FROM #Inventran ASiv  
left outer join #Purcline AS pr on iv.Transrefid=pr.purchid
left outer join #PurcTable AS pt on pt.Purchid=iv.Transrefid

Answer 2

Thank you for your promp reply.

The QtyOrdered should be less by QtyReceived the total will  OpenQty.

the value of OpenQty will be place in QtyOrdered then be less again by QtyRecieved.

This is not exactly im looking for. please see my sample specially the Qty Received column.

Answer 3

Try the below one:

SELECT	PL.PurchID, PL.ItemId, PL.QtyOrdered, ITOuter.Qty QtyReceived, PL.QtyOrdered - Temp.ReceivedQtyTillDate AS OpenQty, PL.PurchPrice, PT.Purchpoolid, PT.VendRef 
FROM		#PurcLine PL
JOIN		#Inventran ITOuter
ON			PL.ItemID	= ITOuter.ItemID
AND			PL.PurchId	= ITOuter.Transrefid
JOIN		#PurcTable PT
ON			PL.PurchId	= PT.Purchid
CROSS APPLY	(
				SELECT	SUM(ITInner.Qty) ReceivedQtyTillDate
				FROM	#Inventran ITInner
				WHERE	ITOuter.ItemID			=	ITInner.ItemID
				AND		ITOuter.Transrefid		=	ITInner.Transrefid
				AND		ITOuter.datephysical	>= 	ITInner.datephysical
			) Temp

Thanks!

Answer 4

Thank you for the reply.

The running balance (OpenQty ) is already correct. mayvbe a little bit modification.

i want like this sample if it's possible. Thanks.

QtyOrdered --QtyReceived-- OpenQty
4900 --------700----------- 4200
4200---------250---------- 3950
3950-------- 100---------- 3850
3850-------- 34 ----------3816
3816-------- 500---------- 3316
3316-------- 500---------- 2816
2816-------- 1615--------- 1201
1201-------- 320---------- 881
881----------500--------- 381
381--------- 381---------- 0

Answer 5

Try the query provided below:

SELECT	PL.PurchID, PL.ItemId, PL.QtyOrdered - ISNULL(Temp2.ReceivedQtyTillPreviousDate, 0) AS QtyOrdered, ITOuter.Qty QtyReceived, PL.QtyOrdered - Temp.ReceivedQtyTillDate AS OpenQty, PL.PurchPrice, PT.Purchpoolid, PT.VendRef 
FROM		#PurcLine PL
JOIN		#Inventran ITOuter
ON			PL.ItemID	= ITOuter.ItemID
AND			PL.PurchId	= ITOuter.Transrefid
JOIN		#PurcTable PT
ON			PL.PurchId	= PT.Purchid
CROSS APPLY	(
				SELECT	SUM(ITInner.Qty) ReceivedQtyTillDate
				FROM	#Inventran ITInner
				WHERE	ITOuter.ItemID			=	ITInner.ItemID
				AND		ITOuter.Transrefid		=	ITInner.Transrefid
				AND		ITOuter.datephysical	>= 	ITInner.datephysical
			) Temp
CROSS APPLY	(
				SELECT	SUM(ITInner.Qty) ReceivedQtyTillPreviousDate
				FROM	#Inventran ITInner
				WHERE	ITOuter.ItemID			=	ITInner.ItemID
				AND		ITOuter.Transrefid		=	ITInner.Transrefid
				AND		ITOuter.datephysical	> 	ITInner.datephysical
			) Temp2

Thanks!

Answer 6

Great. You got it.  Thank you very very much..

Answer 7

What is your SQL Server version? In SQL 2008 and less the set based solutions are slower than traditional cursor based solutions. In SQL Server 2012 you can use window functions with ORDER BY. Check this forum for my recent threads on a similar topic.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

Hi Naomi, Im using MS SQL 2008..

Answer 9

In this case check the links from that thread

MSDN thread with many helpful links

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 10

Hi,

I have additional query regarding your solution.

when i tried on my sctual data. i just encountered a problem.

i notice that i have a double transaction. meaning the same purchid, itemid and physical date but different qty received. the physical date doesnt have the time. see my actual data.

something like this sample:

---------------------------------------------------------------------------------------------------------------
Purchid----Itemid---deliverydate--datephysical--QtyOrdered--QtyReceived--OpenQty--Purchprice--Purchpoolid--VendRef
---------------------------------------------------------------------------------------------------------------
PH0057151--HTC1534--2012/06/30----2012-06-04-----4900-------700----------4200-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-05-----4200-------250----------3950-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-05-----3950-------100----------3850-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-09-----3850-------34 ----------3816-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-14-----3816-------500----------3316-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-16-----3316-------500----------2816-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-18-----2816-------1615---------1201-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-20-----1201 ------320----------881 -----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-21-----881--------500-----------381-----9.00-----------PR-------CM00197
PH0057151--HTC1534--2012/06/30----2012-06-22-----381--------381------------0------9.00-----------PR-------CM00197RECEIVEDDATE PURCHID ITEMID QtyOrdered QTY OPENQTY

Here is my actual data. different physical date or delivery date.
DELIVERYDATE--------------PURCHID---ITEMID---QtyOrdered--------QTY--------------OPENQTY
---------------------------------------------------------------------------------------------
2011-12-02 00:00:00.000--PH0053838--HTC1006--350.000000000000--50.000000000000--0.000000000000
2011-11-27 00:00:00.000--PH0053838--HTC1006--700.000000000000--350.000000000000-350.000000000000
2011-12-02 00:00:00.000--PH0053838--HTC1006--350.000000000000--300.000000000000-0.000000000000