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