473,322 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Design Question

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
2 1402
-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
>> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Don Vaillancourt | last post by:
Hello all, Over the years as I design more database schemas the more I come up with patterns in database design. The more patterns I recognize the more I want to try to design some kind of...
9
by: sk | last post by:
I have an applicaton in which I collect data for different parameters for a set of devices. The data are entered into a single table, each set of name, value pairs time-stamped and associated with...
2
by: Test User | last post by:
Hi all, (please excuse the crosspost as I'm trying to reach as many people as possible) I am somewhat familiar with Access 2000, but my latest project has me stumped. So, I defer to you...
6
by: rodchar | last post by:
Hey all, I'm trying to understand Master/Detail concepts in VB.NET. If I do a data adapter fill for both customer and orders from Northwind where should that dataset live? What client is...
17
by: tshad | last post by:
Many (if not most) have said that code-behind is best if working in teams - which does seem logical. How do you deal with the flow of the work? I have someone who is good at designing, but...
17
by: roN | last post by:
Hi, I'm creating a Website with divs and i do have some troubles, to make it looking the same way in Firefox and IE (tested with IE7). I checked it with the e3c validator and it says: " This...
6
by: JoeC | last post by:
I have a question about designing objects and programming. What is the best way to design objects? Create objects debug them and later if you need some new features just use inhereitance. Often...
0
by: | last post by:
I have a question about spawning and displaying subordinate list controls within a list control. I'm also interested in feedback about the design of my search application. Lots of code is at the...
19
by: neelsmail | last post by:
Hi, I have been working on C++ for some time now, and I think I have a flair for design (which just might be only my imagination over- stretched.. :) ). So, I tried to find a design...
8
by: indrawati.yahya | last post by:
In a recent job interview, the interviewer asked me how I'd design classes for the following problem: let's consider a hypothetical firewall, which filters network packets by either IP address,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.