| Author |
 Topic  |
|
|
smartsl
Starting Member
6 Posts |
 Posted - 12/03/2006 : 17:20:01
|
I am working on developing a web appliation for a small trucking company...one of our customers wants to view their inventory in the warehouse online....as 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. |
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 12/03/2006 : 17:51:33
|
Welcome to the team smartsl!
Something like this: ?
select w.*, theSumPerContent.*, theSumPerContent.inbound-theSumPerContent.outbound as Net
from
wareorders w
join
(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...
<Edit>
PS.
Just to be clear, never use select * in a real production system.
rockmoose |
Edited by - rockmoose on 12/03/2006 17:54:25 |
 |
|
|
smartsl
Starting Member
6 Posts |
Posted - 12/03/2006 : 20:05:06
|
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? |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 12/03/2006 : 20:20:57
|
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
http://www.elsasoft.org |
|
|
|
smartsl
Starting Member
6 Posts |
Posted - 12/03/2006 : 22:19:06
|
These are the specific column names and tables with the needed output, all and any help is appreciatedD
Table 1: WareOrders
Columns Needed:
WareOrders.WareOrder
WareOrders.Status
WareOrders.PickUpDate
WareOrders.InBoundOutBound (Whether "I" or "O")
WareOrders.DeliveryDate
WareOrders.Shipper
WareOrders.PONumber
WareOrders.Consignee
WareOrders.Container
Table 2: WareCommdt
Columns Needed:
WareCommdt.WareOrder
WareCommdt.Units
WareCommdt.Commodity
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! |
|
|
|
smartsl
Starting Member
6 Posts |
Posted - 12/04/2006 : 16:45:04
|
| i'm still looking for help, if you can help with the above pleae let me know... |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 12/04/2006 : 17:20:02
|
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 |
|
|
| |
Topic |
|
Advanced Query Help!
