August 26, 2015 at 5:04 pm
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.
August 26, 2015 at 7:04 pm
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
August 26, 2015 at 7:48 pm
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/
August 26, 2015 at 11:13 pm
Nice solution, but where did OrderDate go?
August 27, 2015 at 1:35 am
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
August 27, 2015 at 6:45 am
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/
August 27, 2015 at 7:43 am
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
Change is inevitable... Change for the better is not.
August 27, 2015 at 1:04 pm
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
August 27, 2015 at 4:24 pm
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply