August 24, 2009 at 3:44 am
Hi all,
Can anyone direct me toward any tutorials concerning conversion of uom's (weight, volume) with sql Server 2008(Express). I have Googled till my fingers bled and haven't found anything geared towards beginners. I am searching for instructions from step one for structure of uom table, factoring(?), etc.
Any help or guidance appreciated,
Mike
August 24, 2009 at 4:13 am
Mike...not sure if there are any tutorials as such for UOM conversion...but obviously you have looked.
I use UOM conversions to allow me to sum products that are sold in differing size packages in a std way.
say for instance you sell
6 * Product A (10kg pack) = 60kg
5 * Product B (50kg pack) = 250kg
2 * Product C (20kg pack) = 40kg
I would then report sales sum for all three products as 350Kg
Is this what you are looking to do?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 24, 2009 at 4:39 am
Hi gah,
Thanks for replying.
The specific project I'm working on will need the ability to compare two uoms and convert to the same base class for a price comparison. for example data from two different sources may be entered in database as such:
Description Pack Size UOM Price
Ground Beef 4 10 LB 59.59
Ground Beef 1 1 LB 1.45
I want to get the per pound price for product #1, which will be displayed in a datagridview in a windows form app. for price comparison.
I hope that is clear.
Thanks again,
Mike
August 24, 2009 at 4:45 am
p.s. I should have also stated that the two example entries are in seperate tables, each vendor having their own.
Mike
August 24, 2009 at 5:13 am
Hi Mike
the way you have described your data you could just run Price/(Pack*Size)
but it may not be as simple as this.
So that neither of us gets confused or misintrepts each other can I please ask that you provide create table statements / some sample data that best represents the possible combinations you have and the results that you wish to return.
If you follow the advice found here:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
we may get a better answer far faster.
ps...you mention that different vendor have separate tables...do they all use the same product code for "Ground Beef"?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 24, 2009 at 5:40 am
Hi,
Here is create statement. The vendor's tables are all same, so just create two with different names:
CREATE TABLE [dbo].[USFoods](
[Product #] [int] NOT NULL,
[Cat_ID] [smallint] NULL,
[SubCat_ID] [smallint] NULL,
[Description] [varchar](255) NULL,
[Brand] [varchar](255) NULL,
[Pack] [varchar](255) NULL,
[Size] [varchar](255) NULL,
[Measure] [varchar](50) NULL,
[Price] [money] NULL
CONSTRAINT [PK_USFoods] PRIMARY KEY CLUSTERED
(
[Product #] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Data Table 1;
111111,3,4,Basil,abc,1,4,OZ,6.15
Data Table 2;
222222,3,4,Basil,xyz,1,1,LB,28.50
I realized that my original post was not a good example of conversion, this hopefully better illustrates need for "apples to apples" comparison for user, needing per oz. price.
Gotta run off to work, will check for reply a.s.a.p.
Thanks again,
Mike
August 24, 2009 at 7:01 am
I also just realized how stupid the types are for "Pack" and "Size", please ignore and use proper types (Int, I suppose) for fields.
Mike
August 24, 2009 at 7:31 am
Hi Mike
I have altered your table structure a little ....
please run code below and see how you get on.
I am concerned that each vendor has their own table and Product Id...you may need to create some type of xref table to manage all the vendor codes for "basil" to a standard code that your app uses for "Basil"
anyway here's some food for thought (no pun intended 🙂 )
Please let me know what you think..regards gah
CREATE TABLE [UKFoods]
(
[Product #] [int] NOT NULL
, [Cat_ID] [smallint] NULL
, [SubCat_ID] [smallint] NULL
, [Description] [varchar](255) NULL
, [Brand] [varchar](255) NULL
, [Measure] [varchar](50) NULL
, [Price] [money] NULL
)
INSERT [dbo].[UKFoods]
VALUES (
222222
, 3
, 4
, 'Basil'
, 'xyz'
, 'LB'
, 28.50
)
CREATE TABLE [USFoods]
(
[Product #] [int] NOT NULL
, [Cat_ID] [smallint] NULL
, [SubCat_ID] [smallint] NULL
, [Description] [varchar](255) NULL
, [Brand] [varchar](255) NULL
, [Measure] [varchar](50) NULL
, [Price] [money] NULL
)
INSERT [dbo].[USFoods]
VALUES (
111111
, 3
, 4
, 'Basil'
, 'abc'
, '4OZ'
, 6.15
)
CREATE TABLE [UOMconvert]
(
[Measue] [char](50) NOT NULL
, [Basemeasure] [char](50) NOT NULL
, [UOMConversion] [numeric](18, 8) NULL
)
INSERT [dbo].[UOMconvert]
VALUES ( '4OZ', 'LB', 4.00000000 )
INSERT [dbo].[UOMconvert]
VALUES ( '4OZ', 'OZ', 0.25000000 )
INSERT [dbo].[UOMconvert]
VALUES ( 'LB', 'LB ', 1.00000000 )
INSERT [dbo].[UOMconvert]
VALUES ( 'LB', 'OZ', 0.06250000 )
INSERT [dbo].[UOMconvert]
VALUES (
'OZ'
, 'LB '
, 16.00000000
)
INSERT [dbo].[UOMconvert]
VALUES ( 'OZ', 'OZ', 1.00000000 )
SELECT dbo.UKFoods.Brand
, dbo.UKFoods.[Product #]
, dbo.UKFoods.Description
, CAST(dbo.UKFoods.Price * dbo.UOMconvert.UOMConversion AS Decimal(8, 2)) AS Baseprice
, dbo.UOMconvert.Basemeasure
FROM dbo.UKFoods
INNER JOIN dbo.UOMconvert
ON dbo.UKFoods.Measure = dbo.UOMconvert.Measue
WHERE ( dbo.UOMconvert.Basemeasure = 'OZ' )
UNION
SELECT dbo.USFoods.Brand
, dbo.USFoods.[Product #]
, dbo.USFoods.Description
, CAST(dbo.USFoods.Price * dbo.UOMconvert.UOMConversion AS Decimal(8, 2)) AS Baseprice
, dbo.UOMconvert.Basemeasure
FROM dbo.USFoods
INNER JOIN dbo.UOMconvert
ON dbo.USFoods.Measure = dbo.UOMconvert.Measue
WHERE ( dbo.UOMconvert.Basemeasure = 'OZ' )
GO
SELECT dbo.UKFoods.Brand
, dbo.UKFoods.[Product #]
, dbo.UKFoods.Description
, CAST(dbo.UKFoods.Price * dbo.UOMconvert.UOMConversion AS Decimal(8, 2)) AS Baseprice
, dbo.UOMconvert.Basemeasure
FROM dbo.UKFoods
INNER JOIN dbo.UOMconvert
ON dbo.UKFoods.Measure = dbo.UOMconvert.Measue
WHERE ( dbo.UOMconvert.Basemeasure = 'LB' )
UNION
SELECT dbo.USFoods.Brand
, dbo.USFoods.[Product #]
, dbo.USFoods.Description
, CAST(dbo.USFoods.Price * dbo.UOMconvert.UOMConversion AS Decimal(8, 2)) AS Baseprice
, dbo.UOMconvert.Basemeasure
FROM dbo.USFoods
INNER JOIN dbo.UOMconvert
ON dbo.USFoods.Measure = dbo.UOMconvert.Measue
WHERE ( dbo.UOMconvert.Basemeasure = 'LB' )
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 24, 2009 at 8:12 am
Thanks for your time and thought! I am at my daily grind right now, so won't be able to try your code until later.
If I understand you correctly concerning the ref table, I believe I have that covered. When the user views the available products from the vendor, it is possible that there may be more than one "Basil" available from any given vendor. I have it set up so the user chooses the product they wish to compare, and the Product # is assigned to that generic product in an "Ordering List" table:
[dbo].[LCProduct](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Cat_ID] [smallint] NULL,
[SubCat_ID] [smallint] NULL,
[Description] [varchar](255) NULL, --Description "Basil"
[US_Product#] [int] NULL, --Could be Prod# for "Basil, Fresh" or "Basil, Bulk" or whatever vendor names product and whichever user chooses.
[Sys_Product#] [int] NULL, -- Same for vendor 2
[PFG_Product#] [int] NULL, --Same for vendor 3
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
In the course of normalization, I am considering breaking this table into three, one for each vendor, haven't gotten to it yet.
Any thoughts on that also welcome.
As soon as I have a chance to try out your suggestions, I'll post back here to let you know how it goes. Thanks again.
Mike
August 28, 2009 at 3:25 pm
Hey gah,
I've been out of town for a few days, just got the chance to delve into your suggestions.
Looking at my data, it seems that, for the most part, Price/(Pack*Size) will work fine, and with the least rewriting of code in my application. There are a few instances where I may use the conversion table, if for no other reason than for the sake of practice and learning. Should you, or anyone reading this, come across any further knowledge on the subject, or have any further advice, I would still like to read/hear it. One can never learn too much!
My next project is going to include many recipes and the base of knowledge you've supplied on conversions will be very useful.
Thanks again, if I get stymied on that project, I'll try to hail you in these forums.
See ya,
Mike
August 28, 2009 at 3:42 pm
1mwalsh1 (8/28/2009)
Hey gah,I've been out of town for a few days, just got the chance to delve into your suggestions.
Looking at my data, it seems that, for the most part, Price/(Pack*Size) will work fine, and with the least rewriting of code in my application. There are a few instances where I may use the conversion table, if for no other reason than for the sake of practice and learning. Should you, or anyone reading this, come across any further knowledge on the subject, or have any further advice, I would still like to read/hear it. One can never learn too much!
My next project is going to include many recipes and the base of knowledge you've supplied on conversions will be very useful.
Thanks again, if I get stymied on that project, I'll try to hail you in these forums.
See ya,
Mike
your welcome and good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply