More than Running Totals...

[Uncle Dickie]

I've hit a wall and would appreciate any pointers in moving forward on some code...

I have some data in the following format:

ID, QtyIN, QtyOUT
1, 30, 0
13, 0, 40
16, 50, 0
32, 0, 40

Basically showing stock movements for a part, purchases and sales.

What I am trying to get is for every QtyOUT the ID for each QtyIN that is required to fulfil the sale. The ID number is always in chronological order so the lower the number the sooner it is in (or out).

So in the case of the above data I would want something along the lines of:

ID, QtyOUT, IDtoFulfil
13, 40, 1 and 16
32, 40, 16

[Rabbit]

You'll need to create two running range queries. One for in and one for out. Then you join the two queries together to find the correct matches. Once you have that, you can use an XML query to string the results together.

A second option is to do it all with a cursor.

[Uncle Dickie]

Thanks Rabbit!

I have something working almost as I want it by comparing running sums for in and out.
It's dog slow - takes about 15 minutes to run - but it is better than the 3 hours that it would take to get the information manually so for the time being I'm calling it good enough!

I have not used cursors before so when I am feeling a bit more adventurous I may revisit this report and see if I can make improvements in which case I may repost, but for the time being, thanks for your pointers.

[Rabbit]

If you want, you can post your query here and we can take a look at optimizing it.

[Uncle Dickie]

Well that was a good way to get me to fix the problem!

I didn't want to look too stupid when I posted my code by missing something obvious so I spent a few minutes looking at it and it now runs in less than 1 second!

My problem was using a view that I had created. This view gets used multiple times and was what the running sums were worked out on. In an earlier attempt at getting the info something I was trying to do meant I couldn't use a temporary table (can't remember what it was) but that is now redundant.

Anyway, below is the code as it now stands and a quick summary of my thought process:

#StockProfile contains my transactional data - everything coming in and going out with a generated ID (myID) putting them in date and transaction type order (i.e due in before due out if they occur on the same date)

#temp1 has the running total of stock coming in by part

#temp2 has the running total of stock due out by part

#temp3 gets the first occurance when some stock is available and the first occurance when all stock is available to fulfil the outgoing order.

The final part just brings it all together and does my specific filtering.

SELECT	a.PartID
		,a.TransDate
		,a.QtyIN
		,a.QtyOUT
		,a.OrderNumber
		,a.LineNumber
		,row_number() OVER (ORDER BY a.Transdate, a.OrderNumber, a.LineNumber)	myID
INTO	#StockProfile
FROM	(
		--Stock On Hand Levels
		SELECT	p.PartID
				,'01/01/1900'					TransDate
				,p.OnHandStockLevel				QtyIN
				,0								QtyOUT
				,'On Hand'						OrderNumber
				,null							LineNumber
		FROM	[123_UK].Structure.Parts p		

		UNION ALL

		--Stock Due In 
		SELECT	pod.PartID
				,pod.ReceiptDate
				,pod.QuantityPurchased - pod.QuantityReceived
				,0
				,'Purchase Orders'
				,null
		FROM	[123_UK].Purchase.PurchaseOrderDetails pod
		WHERE	pod.ReceiptStatusID not in (3,4)

		UNION ALL
		
		--Stock Due Out to Works Orders
		SELECT	wop.PartID
				,wop.PlannedIssueDate
				,0
				,wop.PlannedIssueQuantity - wop.ActualIssueQuantity
				,'Works Orders'
				,null
		FROM	[123_UK].Production.WorksOrderParts wop
		JOIN	[123_UK].Production.WorksOrder wo ON wo.WorksOrderNumber = wop.WorksOrderNumber
		WHERE	wop.WorksOrderIssueStatusCode not in (3,4)
				AND wo.IncompleteTransfers = 1
				AND wop.PlannedIssueDate is not null

		UNION ALL

		--Sales Orders Due Out
		SELECT	sod.PartID
				,sod.DespatchDate
				,0
				,sod.QuantityOrdered - sod.QuantityDespatched
				,sod.SalesOrderNumber
				,sod.LineNumber
		FROM	[123_UK].Sales.SalesOrderDetails sod
		JOIN	[123_UK].Sales.SalesOrders so ON so.SalesOrderNumber = sod.SalesOrderNumber
		WHERE	sod.DespatchStatusID not in (3,4)
	) a
GROUP BY	a.PartID
			,a.TransDate
			,a.QtyIN
			,a.QtyOUT
			,a.OrderNumber
			,a.LineNumber


SELECT		myID
			,PartID
			,(
				SELECT		sum(t1.QtyIN)
				FROM		#StockProfile t1
				WHERE		t1.myID <= ccv.myID
				AND			t1.PartID = ccv.PartID
				GROUP BY	t1.PartID
			) RunningIN
INTO		#temp1
FROM		#StockProfile ccv
WHERE		QtyIN > 0


SELECT		ccv.myID
			,ccv.PartID
			,(
				SELECT		sum(t2.QtyOUT)
				FROM		#StockProfile t2
				WHERE		t2.myID <= ccv.myID
				AND			t2.PartID = ccv.PartID
				GROUP BY	t2.PartID
			) RunningOUT
INTO		#temp2
FROM		#StockProfile ccv
WHERE		ccv.QtyOUT > 0


SELECT		myID			myOutID
			,PartID
			,RunningOUT
			,(
				SELECT	MIN(myID)
				FROM	#temp1 t1
				WHERE	t1.PartID = t2.PartID
					AND	t1.RunningIN >= t2.RunningOUT
			)				myInIDLast
			,(
				SELECT	Max(myID)
				FROM	#temp1 t1
				WHERE	t1.PartID = t2.PartID
					AND	t1.RunningIN < t2.RunningOUT
			)				myInIDFirst
INTO		#temp3
FROM		#temp2 t2


SELECT		ccv.OrderNumber
			,ccv.LineNumber
			,so.SecondSalesReference
			,p.PartNumber
			,convert(nvarchar(25),ccv.TransDate,106)	RequiredDate
			,ccv.QtyOUT
			,CASE
				WHEN ccv3.TransDate = '1900-01-01'
				THEN 'SoH'
				ELSE convert(nvarchar(25),ccv3.TransDate,106)
			END	FirstDropDate
			,ccv3.QtyIN									FirstDropQty
			,CASE
				WHEN ccv2.TransDate = '1900-01-01'
				THEN 'SoH'
				ELSE convert(nvarchar(25),ccv2.TransDate,106)
			END	LastDropDate
			,ccv2.QtyIN									LastDropQty
FROM		#StockProfile ccv
LEFT JOIN	#temp3 t3 ON t3.myOutID  = ccv.myID
LEFT JOIN	#StockProfile ccv2 ON ccv2.myID = t3.myInIDLast
LEFT JOIN	#temp3 t4 ON t4.myOutID  = ccv.myID
LEFT JOIN	#StockProfile ccv3 ON ccv3.myID = t4.myInIDFirst
JOIN		Structure.Parts p ON p.PartID = ccv.PartID
JOIN		Sales.SalesOrders so ON so.SalesOrderNumber = ccv.OrderNumber
WHERE		ccv.QtyOUT > 0
AND			so.CustomerID = 13
AND			ccv.TransDate < GETDATE()+90
ORDER BY	ccv.TransDate


DROP TABLE	#StockProfile
DROP TABLE	#temp1
DROP TABLE	#temp2
DROP TABLE	#temp3

[Rabbit]

Glad you got it working! Good luck with the rest of your project!

[ck9663]

Because this is mostly an RBAR processing, try to create the necessary indexes on your temp tables.

Good Luck!!!


~~ CK