Subract fields from 2 separate tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Subract fields from 2 separate tables?

Author

Topic

o9z
Starting Member

23 Posts

Posted - 2010-09-02 : 09:31:33

Here is what I am wanting to accomplish.

Table 1 contains the capacity of a tank

TotalMat1 TotalMat2 TotalMat3
----- ------ -----
5000 3000 8600

Table 2 contains actual material on hand

Mat1 Mat2 Mat3
----- ------ -----
2000 1000 2600

Mat1 Mat2 Mat3
----- ------ -----
800 1200 1600

I now need to sum the materials in table 2...so I get 2800 for mat1, 2200 for mat2, and 4200 for mat3. Now subract the sums from the capacities. Is there an easy way to do this?

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-02 : 10:07:49

select sum(Mat1) as Mat1,sum(Mat2) as Mat2,sum(Mat3) as Mat3 from
(
select Mat1,Mat2,Mat3 from table1
union all
select Mat1,Mat2,Mat3 from table2
) as t

Madhivanan

Failing to plan is Planning to fail

o9z
Starting Member

23 Posts

Posted - 2010-09-03 : 09:47:40

How will this subtract the sum of the materials from the capacities? Doesn't the union combine the results?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-06 : 04:52:39

quote:
Originally posted by o9z

How will this subtract the sum of the materials from the capacities? Doesn't the union combine the results?

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-07 : 17:49:45

The desigh is wrong. You have repeated groups in violation of First Normal Form (1NF) and you have split attributes. FGix teh dsign, so that all tank attributes are in one table normalize the table.

CREATE TABLE Tanks
(tank_nbr INTEGER NOT NULL PRIMARY KEY,
tank_capacity INTEGER NOT NULL
CHECK (tank_capacity > 0),
tank_contents INTEGER NOT NULL
CHECK (tank_contents >= 0),
CONSTRAINT no_overflow
CHECK (tank_capacity <= tank_contents));

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

o9z
Starting Member

23 Posts

Posted - 2010-09-10 : 09:28:21

I am not sure how the design is incorrect. Here is an actual example of the data I need.

tbl_InventoryEntry(the table that records all inventories)

Material Name | Pounds | DATE

AMVE | 1380 | 9/10/10
AMVE | 1277 | 9/10/10
DMVR | 980 | 9/10/10
DMVR | 500 | 9/10/10
ESNO | 1229 | 9/10/10
BSR | 866 | 9/10/10

tbl_Capacities(the table that holds the max tank capactity)

VEG = (Combination of AMVE and DMVR)
ANI = (Combination of ESNO and BSR)

Material | Pounds

VEG | 5000
ANI | 3000

ESNO + BSR = 2095, so I need to take 3000 - 2095 to find the space available in the tank capacity which is 905 pounds of space.

AMVE + DMVR = 4137, so I need to take 5000 - 4137 to find the space available in the tank capacity which is 863 pounds of space.

Does this make a little more sense?

CSears
Starting Member

39 Posts

Posted - 2010-09-10 : 23:39:39

For starters, you need to get it so that you have some generic way of linking what materials can go to what container. Do you have a linking table? Otherwise this will be near impossible to calculate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources