See if this works for you. You can copy and paste this code to an SSMS window and run it to see what it does.CREATE TABLE #Inventory(PartNumber INT, TotalOnHand INT);
CREATE TABLE #Orders(OrderID INT, PartNumber INT, Qty INT);
INSERT INTO #Inventory VALUES (1454,67),(6542,34);
INSERT INTO #Orders VALUES (11,1454,4),(44,6542,2),(37,1454,43),
(55,1454,18),(67,1454,12),(99,1454,22);
SELECT
o1.*
FROM
#Orders o1
INNER JOIN #Inventory i ON i.PartNumber = o1.PartNumber
CROSS APPLY
(
SELECT SUM(o2.Qty) Consumed FROM #Orders o2
WHERE o2.OrderID <= o1.OrderID
AND o2.PartNumber = o1.PartNumber
) o3
WHERE
o3.Consumed > i.TotalOnHand;
DROP TABLE #Inventory,#Orders
When you post a question, if you post the create DDL for the sample data (like the create table and insert statements that I have in the code above), that makes it easier for someone to write code and test and you will get faster responses.
Subtract Total from a different Table
Posted - 02/07/2013 : 11:52:09
