SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Inventory with running total		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

309 Posts
Posted - 07/23/2012 :  02:51:34  Show Profile  Reply with Quote
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

India
47099 Posts
Posted - 07/23/2012 :  10:49:27  Show Profile  Reply with Quote
see scenario 1

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

309 Posts
Posted - 07/23/2012 :  20:56:39  Show Profile  Reply with Quote
Thanks Visakh. This is very helpful..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 07/23/2012 :  21:23:35  Show Profile  Reply with Quote
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

309 Posts
Posted - 07/28/2012 :  00:38:38  Show Profile  Reply with Quote
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
Edited by - Villanuev on 07/28/2012 08:35:13
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 07/28/2012 :  10:03:30  Show Profile  Reply with Quote
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.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

309 Posts
Posted - 07/30/2012 :  02:47:08  Show Profile  Reply with Quote
Thank you sunitabeck for sharing this idea.
I will modify my query based on your sample. thanks again.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000