SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Subtract Total from a different Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PoseyRobert
Starting Member

USA
6 Posts

Posted - 02/07/2013 :  11:52:09  Show Profile  Reply with Quote
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

Edited by - PoseyRobert on 02/07/2013 12:23:36

James K
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 02/07/2013 :  16:36:37  Show Profile  Reply with Quote
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

USA
6 Posts

Posted - 02/07/2013 :  17:29:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3719 Posts

Posted - 02/07/2013 :  17:49:04  Show Profile  Reply with Quote
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

USA
6 Posts

Posted - 02/07/2013 :  20:13:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000