Advanced Query Help! - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Advanced Query Help!

Author

Topic

smartsl
Starting Member

6 Posts

Posted - 2006-12-03 : 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

3279 Posts

Posted - 2006-12-03 : 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

smartsl
Starting Member

6 Posts

Posted - 2006-12-03 : 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
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-03 : 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 - 2006-12-03 : 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 - 2006-12-04 : 16:45:04

i'm still looking for help, if you can help with the above pleae let me know...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-12-04 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources