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 2000 Forums
 SQL Server Development (2000)
 Advanced Query Help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smartsl
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 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  Show Profile  Reply with Quote
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
Go to Top of Page

smartsl
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

jezemine
Flowing Fount of Yak Knowledge

USA
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
http://www.elsasoft.org
Go to Top of Page

smartsl
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.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!
Go to Top of Page

smartsl
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

rockmoose
SQL Natt Alfen

Sweden
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  
 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.11 seconds. Powered By: Snitz Forums 2000