|
|
You're in the
sql server programming
group:Advice on SQL statement
sql server programming:
Please post your CREATE TABLE statements, sample data as INSERT statements and expected results as a list of values. That will help others understand you core requirements better than wading through a lengthy narrative. Also see : http://www.aspfaq.com/5006 -- - Anith ( Please reply to newsgroups only )
Yes, it is possible to construct a query to produce that result, but I think
you are complicating the issue somewhat. Put the actual measurement (your QTY if I read it correctly) into the INVentory table. From what you say, the INV_TYPE determines the unit of measure, so the UOM table is only useful for controlling the input of new INV_TYPE rows (as a lookup table for an input combo/list box). If you will need to have the Inventory reflect for instance a "pair" as both a unit of two studs, and as two separate units of one stud, you really need a Bill of Materials type breakdown. If you never need to go deeper than one level of combination (suc as that example), you may be able to use a simplified BoM approach, but lets say you have a "set" which contains a necklace plus a pair of studs, you have two levels of breakdown, and you might as well go with the complete BoM breakdown approach, or simply keep your inventory as individual pieces (i.e. lowest level of breakdown) and have the application deal with "sets". Or you could have a "Sets" table with either a one-to-many relationship into the INVentory (individual items belong to predefined sets), or a dynamic sets model with a meny-to-many relationship where any item can be used as an item in any number of different sets. In the latter case, you will have to check for (and resolve) item contention. At the same time, a customer may consider different sets centered around one of the pieces. Disregarding the "sets" issue, you should be able to do something like: Table INV_TYPE: ============= INV_CODE PRICE (per unit) Unit Type .... Table INV: ======== INV_ID ACCOUNT INV_CODE Measured QTY/Value in Unit Type (i.e. x Carat, x Grams, etc.) .... I would add that your INV_TYPE appears denormalized, since you are storing a combination of the raw material type and its grading in a single column, but in your case that may be nitpicking. HTH, Tore. [quoted text, click to view] "Alan Kordy" <alan_kordy@kordina.com.sg> wrote in message
news:OkistN5nDHA.3688@TK2MSFTNGP11.phx.gbl... > We wanted to create a database for a jewellery manufacturer. A common table > will be required to store all sort of raw materials (e.g. different > type/grade of diamonds, stones, metals, and etc.). Due to different type of > material uses different UOM (unit of measurement), we created a header table > for inventory, and a detail table for UOM (qty, weight, and etc). Below are > the design, with sample data: > > > INV_TYPE (Inventory type, a master table) > ================================ > INV_CODE PRICE ...other fields... > -------------------------------------------------------------------------- -- > - > DIAMOND_GVVS 201.25 ... > DIAMOND_IVVS 150.20 ... > RUBY_CLASS1 102.50 ... > PEARL_001 56.00 ... > YELLOW_GOLD_999 23.56 ... > WHITE_GOLD_750 24.00 ... > > > UOM (master table for all types of UOM) > =============================== > UOM_CODE ...other fields... > --------------------------------------------------------- > WEIGHT ... > CARAT ... > QTY ... > > > INV (inventory table to store all available materials) > ======================================= > INV_ID ACCOUNT INV_CODE ...other fields... > -------------------------------------------------------------------------- -- > ---------------- > 001 MICHAEL YELLOW_GOLD_999 ... > 002 DAVID YELLOW_GOLD_999 ... > 003 MICHAEL DIAMOND_GVVS ... > 004 MICHAEL RUBY_CLASS1 ... > 005 MICHAEL PEARL_001 ... > > > INV_UOM (UOM for each raw material according to table above) > ================================================== > INV_ID UOM_CODE VALUE ...other fields... > -------------------------------------------------------------------------- -- > ----------- > 001 WEIGHT 12.53 ... > 002 WEIGHT 20.45 ... > 003 CARAT 0.40 ... > 003 QTY 4 > ... > 004 WEIGHT 0.66 ... > 004 QTY 3 .... > 005 QTY 2 .... > > > Why we have to design the table this way? Some might ask, we can include all > types of UOM as a field into INV table. So the design will be something like > this: > > INV > === > INV_ID > ACCOUNT > INV_CODE > WEIGHT > CARAT > QTY > ...other fields... > > Actually, we can't. This is due to the type and number of UOM required for > different inventory type could be different. AS sample given above, > DIAMONDs, are count by QTY, as well as CARAT (we want to keep the total QTY > as well as total CARAT for particular calss of DIAMOND), GOLD count by > WEIGHT, PERAL by QTY, RUBY by QTY and WEIGHT, and etc. > > Beside that, UOM for some inventory types are unpredictable. For example, > later, we might have a new type of UOM called "CM" for YELLOW_GOLD_CHAIN, or > we might have a new UOM called "PAIR" for EARRING_STUD, or UOM called "SET" > for certain type of JEWELLERY_SET. So, by having a UOM master table, we can > just add new UOM when it is required, and no changes to the table design > will be needed. > > We had completed with the user interface to deal with all this, but we have > a problem for reports. For example, we want to have a report in this format: > > > ACCOUNT INV_CODE CARAT QTY WEIGHT > (CM, PAIR, and etc. will appear when it is added) > -------------------------------------------------------------------------- -- > -------------------------------------------------------------------------- -- > -------------- > MICHAEL YELLOW_GOLD_999 12.53 > MICHAEL DIAMOND_GVVS 0.40 4 > MICHAEL RUBY_CLASS1 3 > 0.66 > MICHAEL PEARL_001 2 > DAVID YELLOW_GOLD_999 20.45 > > So, the question is, is there anyway we can write a query to return results > as shown above? Or what type of join we can use? Or we have to create a temp > table? Actually, we can't hardcode the UOM type added to the UOM master > table in our query. If we hardcoded the UOM into our query, next time when
We wanted to create a database for a jewellery manufacturer. A common table
will be required to store all sort of raw materials (e.g. different type/grade of diamonds, stones, metals, and etc.). Due to different type of material uses different UOM (unit of measurement), we created a header table for inventory, and a detail table for UOM (qty, weight, and etc). Below are the design, with sample data: INV_TYPE (Inventory type, a master table) ================================ INV_CODE PRICE ...other fields... ---------------------------------------------------------------------------- - DIAMOND_GVVS 201.25 ... DIAMOND_IVVS 150.20 ... RUBY_CLASS1 102.50 ... PEARL_001 56.00 ... YELLOW_GOLD_999 23.56 ... WHITE_GOLD_750 24.00 ... UOM (master table for all types of UOM) =============================== UOM_CODE ...other fields... --------------------------------------------------------- WEIGHT ... CARAT ... QTY ... INV (inventory table to store all available materials) ======================================= INV_ID ACCOUNT INV_CODE ...other fields... ---------------------------------------------------------------------------- ---------------- 001 MICHAEL YELLOW_GOLD_999 ... 002 DAVID YELLOW_GOLD_999 ... 003 MICHAEL DIAMOND_GVVS ... 004 MICHAEL RUBY_CLASS1 ... 005 MICHAEL PEARL_001 ... INV_UOM (UOM for each raw material according to table above) ================================================== INV_ID UOM_CODE VALUE ...other fields... ---------------------------------------------------------------------------- ----------- 001 WEIGHT 12.53 ... 002 WEIGHT 20.45 ... 003 CARAT 0.40 ... 003 QTY 4 .... 004 WEIGHT 0.66 ... 004 QTY 3 ... 005 QTY 2 ... Why we have to design the table this way? Some might ask, we can include all types of UOM as a field into INV table. So the design will be something like this: INV === INV_ID ACCOUNT INV_CODE WEIGHT CARAT QTY ....other fields... Actually, we can't. This is due to the type and number of UOM required for different inventory type could be different. AS sample given above, DIAMONDs, are count by QTY, as well as CARAT (we want to keep the total QTY as well as total CARAT for particular calss of DIAMOND), GOLD count by WEIGHT, PERAL by QTY, RUBY by QTY and WEIGHT, and etc. Beside that, UOM for some inventory types are unpredictable. For example, later, we might have a new type of UOM called "CM" for YELLOW_GOLD_CHAIN, or we might have a new UOM called "PAIR" for EARRING_STUD, or UOM called "SET" for certain type of JEWELLERY_SET. So, by having a UOM master table, we can just add new UOM when it is required, and no changes to the table design will be needed. We had completed with the user interface to deal with all this, but we have a problem for reports. For example, we want to have a report in this format: ACCOUNT INV_CODE CARAT QTY WEIGHT (CM, PAIR, and etc. will appear when it is added) ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- -------------- MICHAEL YELLOW_GOLD_999 12.53 MICHAEL DIAMOND_GVVS 0.40 4 MICHAEL RUBY_CLASS1 3 0.66 MICHAEL PEARL_001 2 DAVID YELLOW_GOLD_999 20.45 So, the question is, is there anyway we can write a query to return results as shown above? Or what type of join we can use? Or we have to create a temp table? Actually, we can't hardcode the UOM type added to the UOM master table in our query. If we hardcoded the UOM into our query, next time when there is a new UOM added to the UOM master table, we will need to change the query, which is what we hope to avoid. Really thanks a lot for your patient for finish reading this.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |