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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Advanced Query Help!
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

6 Posts

Posted - 12/03/2006 :  17:20:01  Show Profile  Reply with Quote
I am working on developing a web appliation for a small trucking of our customers wants to view their inventory in the warehouse so that is what i am doing.

I have two tables one is "wareorders" and the other is "warecommdt." In wareorders we have all of the 'events' for a particular order and in warecommdt, we have all of the 'contents' of that order. For example: order# 10000's events would be "Inbound, 10/10/2006, etc." and the contents would be "7 units, lumber."

In both tables, there is a "WareOrder" colum that links both tables together.

I need to design a query that

1) will pull all colums from the wareorders table;
2) will pull the number of units and commodity from the warecommdt table;
3) will be grouped by the wareorder colum.

Finally, I need the query to be able to
1) add all "inbound" units together for a particular order
2) add all "outbound" units together for a particular order
3) then subtract the outbound from the inbound to get the total number of units in the warehouse.

Any and all help on this is greatly appreciated, I can provide more info at your request.

SQL Natt Alfen

3279 Posts

Posted - 12/03/2006 :  17:51:33  Show Profile  Reply with Quote
Welcome to the team smartsl!

Something like this: ?

select w.*, theSumPerContent.*, theSumPerContent.inbound-theSumPerContent.outbound as Net
wareorders w
(select c.wareorder, c.content, sum(case when "inbound" then c.units else 0 end) as inbound, sum(case when "outbound" then c.units else 0 end) as outbound
from warecommdt c
group by c.wareorder, c.content ) as theSumPerContent
on w.wareorder = theSumPerContent.wareorder

Post what you got...

Just to be clear, never use select * in a real production system.


Edited by - rockmoose on 12/03/2006 17:54:25
Go to Top of Page

Starting Member

6 Posts

Posted - 12/03/2006 :  20:05:06  Show Profile  Reply with Quote
hi rockmoose

i am sort of a sql-novice, so i'm still learning the language and so on.

would it be helpful if i posted exact table/column names for you so that i can better understand?
Go to Top of Page

Flowing Fount of Yak Knowledge

2886 Posts

Posted - 12/03/2006 :  20:20:57  Show Profile  Visit jezemine's Homepage  Reply with Quote
a full specification of the problem is always helpful to those trying to provide a solution

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
Go to Top of Page

Starting Member

6 Posts

Posted - 12/03/2006 :  22:19:06  Show Profile  Reply with Quote
These are the specific column names and tables with the needed output, all and any help is appreciatedD

Table 1: WareOrders
Columns Needed:
WareOrders.InBoundOutBound (Whether "I" or "O")

Table 2: WareCommdt
Columns Needed:

The "WareOrder" column will link the two tables together.

I need to:
1) Select all of the orders from the WareOrders table;
2) In that tow, I need to link that order to the WareCommdt table to retrieve that order's Units and Commodity
3) Finally, I need to group the data by the WareCommdt.Commodity column and add up the total number of units of that commodity by: adding up all of the Inbound Orders by (WareOrders.InboundOutBound = "I") and subtracting all of the outbound orders for that commodity (WareOrders.InboundOutBound = "O") to get the total Net Units

All help is apprecaited...this is above my head!
Go to Top of Page

Starting Member

6 Posts

Posted - 12/04/2006 :  16:45:04  Show Profile  Reply with Quote
i'm still looking for help, if you can help with the above pleae let me know...
Go to Top of Page

SQL Natt Alfen

3279 Posts

Posted - 12/04/2006 :  17:20:02  Show Profile  Reply with Quote
select w.wareorder, wc.commodity, sum(case when w.inboundoutbound = 'I' then wc.units else -wc.units end) as netunits
from wareorders w join warecommdt wc on w.wareorder = wc.wareorder
where w.inboundoutbound in ('I','O') -- not needed if this is ALWAYS the case
group by w.wareorder, wc.commodity

To get the other columns, one possibility is to just add them in the "select" and the "group by" clause
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000