By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,576 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,576 IT Pros & Developers. It's quick & easy.

Design Question

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-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
Jul 20 '05 #2

P: n/a
>> 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.
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.