Inventory with running total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Inventory with running total

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-07-23 : 02:51:34

Hi Forumer's

I though there's already an existing sample in this forum.
I want ot do an inventory with running balance.
I could not figure how to make this kind of approach.

Help is very much appreciated. Thanks.


Sample Data:

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)



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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 10:49:27

see scenario 1

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-07-23 : 20:56:39

Thanks Visakh. This is very helpful..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 21:23:35

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-07-28 : 00:38:38

Hi Guys,

This script is working. When encountering the records
with the same DatePhysical the calculation/computation for balance is not correct.
the datephysical does not have the time. can you help me guys to fix this issue.
another thing running this query it takes time.any idea guys. thanks in advance.

here is my sample data:


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-20','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)


This is the 2 records with the same DatePhysical.
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-20','PH0057151',500)



script:

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-28 : 10:03:30

To get around the problem of more than one row with the same datephysical, you have to find some way to order such duplicates so that they can be processed in a well-defined order. For example, if you have the ability to do so, you could add another column and populate it with a serial number like this:

ALTER TABLE #Inventran ADD SequenceNum INT;
;WITH cte AS
(
	SELECT SequenceNum,ROW_NUMBER() OVER (ORDER BY datephysical,Qty) AS RN
	FROM #Inventran
)
UPDATE cte SET SequenceNum = RN;

Then, change you subqueries to use that instead of the datephysical:

...
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
				AND		ITOuter.SequenceNum	>= 	ITInner.SequenceNum
			) Temp
....

For speeding up the query, I have two thoughts, both just thoughts, so take it for what it is worth:

a) It looks like you have two very similar CROSS APPLY sections. May be it is possible to change it so there is only one of those - perhaps like shown below. But you will need to test whether this gives the expected results in all cases:

SELECT PL.PurchID,
       PL.ItemId,
       PL.QtyOrdered - ISNULL(Temp2.ReceivedQtyTillPreviousDate, 0) AS QtyOrdered,
       ITOuter.Qty QtyReceived,
       PL.QtyOrdered - (ITOuter.Qty+ISNULL(Temp2.ReceivedQtyTillPreviousDate,0)) 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.SequenceNum	>= 	ITInner.SequenceNum
--			) Temp
CROSS APPLY	(
				SELECT	SUM(ITInner.Qty) ReceivedQtyTillPreviousDate
				FROM	#Inventran ITInner
				WHERE	ITOuter.ItemID			=	ITInner.ItemID
				AND		ITOuter.Transrefid		=	ITInner.Transrefid
				AND		ITOuter.SequenceNum	> 	ITInner.SequenceNum
			) Temp2

b)The second thing is whether you have appropriate (or any) indexes on the tables. I am thinking these:

PurchId,ItemID on #PurcLine
Transrefid,ItemID on #Inventran
PurchId on #PurcTable
But I am shooting from the hip with regards to these indexes. So I will disown them if they turn out to be no good.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-07-30 : 02:47:08

Thank you sunitabeck for sharing this idea.
I will modify my query based on your sample. thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources