Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Subtract Total from a different Table

Author  Topic 

PoseyRobert
Starting Member

27 Posts

Posted - 2013-02-07 : 11:52:09
I have been trying to figure out the best way to tackle this issue.

I have a Table A that contains a total. I want to subtract from Table A using TABLE B. I only want to show the items that will show as a negative from TABLE B.

Table A

Part_Number Total_On_Hand
1454__________ 67
6542__________ 34

Table B

Order_ID____ Part# Qty
11__________ 1454 4
44__________ 6542 2
37__________ 1454 43
55__________ 1454 18
67__________ 1454 12
99__________ 1454 22


Result:
Order_ID____ Part# Qty
67__________ 1454 12
99__________ 1454 22

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 16:36:37
I didn't quite follow what you meant by " I only want to show the items that will show as a negative from TABLE B.. Is TableA the inventory that you have on hand, and TableB the orders that you want to fill, and you want to start filling orders until you don't have enough, and then want to display the unfilled orders?

All of that can be done - but to be able to do that, you need to be able to order the data in TableB. This is required to make a determination on which order should be filled first, which one second etc. If you don't care about which orders are filled and which ones are left unfilled, that can be done too. Can you clarify?
Go to Top of Page

PoseyRobert
Starting Member

27 Posts

Posted - 2013-02-07 : 17:29:58
Hi James K,

Yes.... you got it.

I would like to fill the oldest to newest orders first.

Thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 17:49:04
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.
Go to Top of Page

PoseyRobert
Starting Member

27 Posts

Posted - 2013-02-07 : 20:13:21
Thanks James K,

This is what I am looking for.

Next time I will post the information correctly.

Thanks again.
Go to Top of Page
   

- Advertisement -