Design Question

[Boogieboy]

Hi

I am trying to implement a invtentory control system and would like
some advice on the best design for it.

The system will have to main tables Product and Stock which will look
as follows

Product
-------
ProductId
PartNumber
Description

Stock
-----
StockId
ProductId
SerialNumber
RecievedDate
OrderNo
ShipmentNo

In ths stock table the RecivedDate Signifies when the product
recieved, The OrderNo signifies the whether the item has been sold and
the shipmentNo represents whethert tiem has been shipped.

I want to produce an SQL query which basically looks like this

StockList
---------
PartNumber
Description
QtyInStock (StockItems not sold or shipped)
QtySold (StockItems Sold)
QtyShipped (StockItems Sold and shipped)

I cant seem to work out what the query would look like for this.
Has anyone got anytips, or alternative ideas/designs

[-P-]

"Boogieboy" <ia*******@hotmail.com> wrote in message news:67**************************@posting.google.c om...

Hi

I am trying to implement a invtentory control system and would like
some advice on the best design for it.
<snip>
I want to produce an SQL query which basically looks like this

StockList
---------
PartNumber
Description
QtyInStock (StockItems not sold or shipped)
QtySold (StockItems Sold)
QtyShipped (StockItems Sold and shipped)

I cant seem to work out what the query would look like for this.
Has anyone got anytips, or alternative ideas/designs

Tip: Summing 1s and 0s is essentially the same as counting...

How about this?

Select
PartNumber,
Description,
SUM( CASE
WHEN OrderNo is NULL and ShipmentNo is NULL
then 1 else 0 END) as QtyInStock,
SUM( CASE
WHEN OrderNo is NOT NULL and ShipmentNo is NULL
then 1 else 0 END) as QtySold,
SUM( CASE
WHEN OrderNo is NOT NULL and ShipmentNo is NOT NULL
then 1 else 0 end) as QtyShipped
FROM
Product
JOIN Stock
ON Product.ProductID = Stock.ProductID
GROUP BY
PartNumber,
Description
ORDER BY
PartNumber ;

--
Paul Horan[TeamSybase] www.teamsybase.com
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com

[--CELKO--]

>> Has anyone got any tips, or alternative ideas/designs <<

inventory, orders and shipments are all logically different things, I
would put them in separate tables.