Need to keep a running total of OnHandQty Minus OrderQty

Question

Need to keep a running total of OnHandQty Minus OrderQty

ducksql@gmail.com

SSCrazy

Points: 2826
More actions
August 26, 2015 at 5:04 pm

#307220

I have been tasked with writing a report that shows all open orders for an item and their quantities, along with a running total of what is left in stock. We start by building these two tables:
IF OBJECT_ID('tempdb..#Orders', 'U') IS NOT NULL DROP TABLE #Orders;
IF OBJECT_ID('tempdb..#Warehouse', 'U') IS NOT NULL DROP TABLE #Warehouse;
CREATE TABLE #Orders (OrderDate DATETIME, JobNumber NVARCHAR(10), Item NVARCHAR(20), QtyOrdered NUMERIC(10, 2))
INSERT INTO #Orders SELECT '20150801', 'JOB1', 'Widget1', 5
INSERT INTO #Orders SELECT '20150802', 'JOB2', 'Widget1', 3
INSERT INTO #Orders SELECT '20150803', 'JOB3', 'Widget1', 2
INSERT INTO #Orders SELECT '20150801', 'JOB4', 'Widget2', 4
INSERT INTO #Orders SELECT '20150802', 'JOB5', 'Widget2', 2
INSERT INTO #Orders SELECT '20150803', 'JOB6', 'Widget2', 6
CREATE TABLE #Warehouse (Item NVARCHAR(20), QtyOnHand NUMERIC(10, 2))
INSERT INTO #Warehouse SELECT 'Widget1', 14
INSERT INTO #Warehouse SELECT 'Widget2', 10
SELECT * FROM #Orders
SELECT * FROM #Warehouse
IF OBJECT_ID('tempdb..#Orders', 'U') IS NOT NULL DROP TABLE #Orders;
IF OBJECT_ID('tempdb..#Warehouse', 'U') IS NOT NULL DROP TABLE #Warehouse;
which gives us these results:
OrderDate JobNumber Item QtyOrdered
----------------------- ---------- -------------------- ---------------------------------------
2015-08-01 00:00:00.000 JOB1 Widget1 5.00
2015-08-02 00:00:00.000 JOB2 Widget1 3.00
2015-08-03 00:00:00.000 JOB3 Widget1 2.00
2015-08-01 00:00:00.000 JOB4 Widget2 4.00
2015-08-02 00:00:00.000 JOB5 Widget2 2.00
2015-08-03 00:00:00.000 JOB6 Widget2 6.00
(6 row(s) affected)
Item QtyOnHand
-------------------- ---------------------------------------
Widget1 14.00
Widget2 10.00
(2 row(s) affected)
Now I want to run a query that will give me these results:
OrderDate JobNumber Item QtyOrdered QtyOnHand
----------------------- ---------- -------- ------------ -----------
2015-08-01 00:00:00.000 JOB1 Widget1 5.009.00
2015-08-02 00:00:00.000 JOB2 Widget1 3.006.00
2015-08-03 00:00:00.000 JOB3 Widget1 2.004.00
2015-08-01 00:00:00.000 JOB4 Widget2 4.006.00
2015-08-02 00:00:00.000 JOB5 Widget2 2.004.00
2015-08-03 00:00:00.000 JOB6 Widget2 6.00-2.00
(6 row(s) affected)
What is the best way to do this?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63509 More actions · Answer 1

I did the query in 2012 (Yes, I did read that you were using 2008... solution to follow) This was the SQL statement - super easy once Windowing functions are available:

SELECT o.OrderDate

, o.JobNumber

, o.item as OrderItem

, o.QtyOrdered AS Qty

, w.qtyOnHand

, w.qtyOnHand - SUM(o.qtyOrdered) OVER (PARTITION BY o.Item ORDER BY o.OrderDate) AS CurrQty

FROM Orders o INNER JOIN Warehouse w ON o.Item=w.Item

ORDER BY OrderItem, OrderDate;

Okay, since we can't do that in 2008, I did the whole thing in SSRS.

Steps:

1. Create a dataset for Warehouse:

SELECT Item, QtyOnHand

FROM Warehouse

2. Create another dataset for Orders:

SELECT OrderDate, JobNumber, Item, QtyOrdered

FROM Orders

3. Add a tablix to your report.

4. Add a Item field to the RowGroups section.

Add the following columns to your tablix:

Col1: OrderDate

Col2: JobNumber

Col3: Item

Col4: QtyOrdered

Col5: RunningTotalQtyOrdered =RunningValue(Fields!QtyOrdered.Value,Sum,"Item1")

Col6: RunningQOH =Lookup(Fields!Item.Value, Fields!Item.Value, Fields!QtyOnHand.Value, "Warehouse") - RunningValue(Fields!QtyOrdered.Value,Sum,"Item1")

Note, you have to add a RowGroup named "Item1" to the tablix, before you can create the running total that works properly.

Then you should be off and running.

HTH

(learned something new today!)

Pieter

LinksUp SSCertifiable Points: 6526 More actions · Answer 2

sdownen05 (8/26/2015)
Now I want to run a query that will give me these results:
OrderDate JobNumber Item QtyOrdered QtyOnHand
----------------------- ---------- -------- ------------ -----------
2015-08-01 00:00:00.000 JOB1 Widget1 5.009.00
2015-08-02 00:00:00.000 JOB2 Widget1 3.006.00
2015-08-03 00:00:00.000 JOB3 Widget1 2.004.00
2015-08-01 00:00:00.000 JOB4 Widget2 4.006.00
2015-08-02 00:00:00.000 JOB5 Widget2 2.004.00
2015-08-03 00:00:00.000 JOB6 Widget2 6.00-2.00
What is the best way to do this?

Best way? Get SQL 2012 or 2014 or 2016. . .

In the mean time try this 2008 query. It is based on code from Itzik Ben-Gan's book, MS SQL Server 2012 . . Window Functions. But realize, depending upon the number of Items in a window, this can be slow.

SELECT o1.JobNumber, o1.Item, o1.QtyOrdered,

MAX(w.QtyOnHand) - SUM(o2.QtyOrdered) as CurrBalance

FROM #Orders o1

INNER JOIN #WareHouse w on w.Item = o1.item

INNER JOIN #Orders o2

ON o1.Item = o2.Item

AND o2.JobNumber <= o1.JobNumber

GROUP BY o1.Item, o1.JobNumber, o1.QtyOrdered

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

pietlinden SSC Guru Points: 63509 More actions · Answer 3

pietlinden

SSC Guru

Points: 63509

August 26, 2015 at 11:13 pm

#1822657

Nice solution, but where did OrderDate go?

ducksql@gmail.com SSCrazy Points: 2826 More actions · Answer 4

Here it is by OrderDate. Thank you for the solution. That works very well. I can't use the SSRS solution because this data has to go into a Data Warehouse

SELECT o1.OrderDate, o1.JobNumber, o1.Item, o1.QtyOrdered,

MAX(w.QtyOnHand) - SUM(o2.QtyOrdered) as CurrBalance

FROM #Orders o1

INNER JOIN #WareHouse w on w.Item = o1.item

INNER JOIN #Orders o2

ON o1.Item = o2.Item

AND o2.OrderDate <= o1.OrderDate

GROUP BY o1.Item, o1.JobNumber, o1.QtyOrdered, o1.OrderDate

ORDER BY o1.Item, o1.OrderDate, o1.JobNumber

LinksUp SSCertifiable Points: 6526 More actions · Answer 5

pietlinden (8/26/2015)
Nice solution, but where did OrderDate go?

Slight oversight on my part!:-P

Looks like the OP was able to make the necessary adjustments.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

sdownen05 (8/27/2015)
Here it is by OrderDate. Thank you for the solution. That works very well. I can't use the SSRS solution because this data has to go into a Data Warehouse
SELECT o1.OrderDate, o1.JobNumber, o1.Item, o1.QtyOrdered,
MAX(w.QtyOnHand) - SUM(o2.QtyOrdered) as CurrBalance
FROM #Orders o1
INNER JOIN #WareHouse w on w.Item = o1.item
INNER JOIN #Orders o2
ON o1.Item = o2.Item
AND o2.OrderDate <= o1.OrderDate
GROUP BY o1.Item, o1.JobNumber, o1.QtyOrdered, o1.OrderDate
ORDER BY o1.Item, o1.OrderDate, o1.JobNumber

Be careful, now. You'll have to watch that over time because it contains an insidious little monster known as a "Triangular Join", which can eat the face off of your server CPU and IO-wise depending on the row counts of each group. Please see the following article for more information on that particular danger.

http://www.sqlservercentral.com/articles/T-SQL/61539/

On pre-2012 servers, a properly written WHILE loop will always use fewer reads and frequently beat Triangular Joins for performance. Also, a "Quirky Update" will absolutely smoke both methods for performance but does take a bit of time and attention to detail to setup properly.

Like I said, watch the performance and resource usage on the method you have above. If it becomes a problem, post back and we'll help you fix it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ducksql@gmail.com SSCrazy Points: 2826 More actions · Answer 7

Jeff,

Thanks for the tips. Two things. Right now, our data set is small enough that the performance hit will be negligible; however it is expected to increase, so I will be proactive with it.

Second, the link you sent doesn't work for me. Could I kindly ask you to re-send it?

Thank you.

Steve

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

sdownen05 (8/27/2015)
Jeff,
Thanks for the tips. Two things. Right now, our data set is small enough that the performance hit will be negligible; however it is expected to increase, so I will be proactive with it.
Second, the link you sent doesn't work for me. Could I kindly ask you to re-send it?
Thank you.
Steve

My apologies. It would help if I linked it as a URL instead of an image. :blush: Here's the clickable link...

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)