How to use or make an UOM table

Question

Post reply

How to use or make an UOM table

BuilderBits

SSCertifiable

Points: 6071
More actions
June 5, 2006 at 6:37 pm

#172743

Hey sqlers,
One of the concerns the first time i was asking about this is that i may have ten company's that all sell the same product and that product is that same exact product for all the companys ; however, each company sells the product and different prices. But the Length and Width UOM is the same, because it is the same product.
What i need is a resource that can explain to me how to charge based on the Length x Width.
so Product A has a Length 66" x Width 33 1/2":
Based on the length and width of Product A this totals up to $40.00 dollars.
Now if Product A has Measurements of Length 68" x Width 34 1/2": then
Based on the length and width of Product A this totals up to $60.00 dollars.
Is it posiable to have a base price table (Maybe the lowest price that a specific product sells for) and then based on companyID that the Price would become the base price plus a %percentage more?
Is there a such? as percent table? So based on the productId and UOM and CompanyID?
Thanks..
erik
if anyone has it scripted already that would be nice also..
I am not sure if this is what i need but AdventureWorks database they have a -- UnitMeasure -- table..

Here is some script that might help me get someone started that can help me..

Thanks for any help
erik

/****** Object: Table [dbo].[ProductOpt] Script Date: 06/05/2006 17:34:30 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ProductOpt](
[ProductID] [int]
NOT NULL,
[CompanyID] [uniqueidentifier]
NOT NULL,
[OptionName] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost] [money]
NULL CONSTRAINT [d_Cost17] DEFAULT ((0)),
[OptionCD] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OptionValue] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsLockedOut] [bit]
NULL,
[UOM] [varchar]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ProductOpt] PRIMARY KEY CLUSTERED
(
[OptionCD]
ASC,
[CompanyID]
ASC,
[ProductID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [FK_ProductOpt_Product] FOREIGN KEY([ProductID])
REFERENCES
[dbo].[Product] ([ProductID])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [FK_ProductOpt_Product]
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [is_prod_measure_for] FOREIGN KEY([UOM])
REFERENCES
[dbo].[UOM] ([UOM])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [is_prod_measure_for]
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [CK_IsLockedOut9] CHECK (([IsLockedOut]=(0) OR [IsLockedOut]=(1)))
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [CK_IsLockedOut9]
USE
[SuperCenter]
GO
/****** Object: Table [dbo].[ProductOpt] Script Date: 06/05/2006 17:34:30 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ProductOpt](
[ProductID] [int]
NOT NULL,
[CompanyID] [uniqueidentifier]
NOT NULL,
[OptionName] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost] [money]
NULL CONSTRAINT [d_Cost17] DEFAULT ((0)),
[OptionCD] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OptionValue] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsLockedOut] [bit]
NULL,
[UOM] [varchar]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ProductOpt] PRIMARY KEY CLUSTERED
(
[OptionCD]
ASC,
[CompanyID]
ASC,
[ProductID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [FK_ProductOpt_Product] FOREIGN KEY([ProductID])
REFERENCES
[dbo].[Product] ([ProductID])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [FK_ProductOpt_Product]
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [is_prod_measure_for] FOREIGN KEY([UOM])
REFERENCES
[dbo].[UOM] ([UOM])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [is_prod_measure_for]
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [CK_IsLockedOut9] CHECK (([IsLockedOut]=(0) OR [IsLockedOut]=(1)))
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [CK_IsLockedOut9]
USE
[SuperCenter]
GO
/****** Object: Table [dbo].[ProductOpt] Script Date: 06/05/2006 17:34:30 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ProductOpt](
[ProductID] [int]
NOT NULL,
[CompanyID] [uniqueidentifier]
NOT NULL,
[OptionName] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost] [money]
NULL CONSTRAINT [d_Cost17] DEFAULT ((0)),
[OptionCD] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OptionValue] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsLockedOut] [bit]
NULL,
[UOM] [varchar]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ProductOpt] PRIMARY KEY CLUSTERED
(
[OptionCD]
ASC,
[CompanyID]
ASC,
[ProductID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [FK_ProductOpt_Product] FOREIGN KEY([ProductID])
REFERENCES
[dbo].[Product] ([ProductID])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [FK_ProductOpt_Product]
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [is_prod_measure_for] FOREIGN KEY([UOM])
REFERENCES
[dbo].[UOM] ([UOM])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [is_prod_measure_for]
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [CK_IsLockedOut9] CHECK (([IsLockedOut]=(0) OR [IsLockedOut]=(1)))
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [CK_IsLockedOut9]

/****** Object: Table [dbo].[ProductOpt] Script Date: 06/05/2006 17:34:30 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ProductOpt](
[ProductID] [int]
NOT NULL,
[CompanyID] [uniqueidentifier]
NOT NULL,
[OptionName] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Cost] [money]
NULL CONSTRAINT [d_Cost17] DEFAULT ((0)),
[OptionCD] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OptionValue] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsLockedOut] [bit]
NULL,
[UOM] [varchar]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ProductOpt] PRIMARY KEY CLUSTERED
(
[OptionCD]
ASC,
[CompanyID]
ASC,
[ProductID]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [FK_ProductOpt_Product] FOREIGN KEY([ProductID])
REFERENCES
[dbo].[Product] ([ProductID])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [FK_ProductOpt_Product]
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [is_prod_measure_for] FOREIGN KEY([UOM])
REFERENCES
[dbo].[UOM] ([UOM])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [is_prod_measure_for]
GO
ALTER
TABLE [dbo].[ProductOpt] WITH CHECK ADD CONSTRAINT [CK_IsLockedOut9] CHECK (([IsLockedOut]=(0) OR [IsLockedOut]=(1)))
GO
ALTER
TABLE [dbo].[ProductOpt] CHECK CONSTRAINT [CK_IsLockedOut9]
===============================================================================

/****** Object: Table [dbo].[ProductOptCh] Script Date: 06/05/2006 17:34:39 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING ON
GO
CREATE
TABLE [dbo].[ProductOptCh](
[CompanyID] [uniqueidentifier]
NOT NULL,
[IsLockedOut] [bit]
NULL CONSTRAINT [DF_ProductOptCh_IsLockedOut] DEFAULT ((0)),
[ChoiceCD] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProductID] [int]
NOT NULL,
[OptionCD] [varchar]
(5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ChoiceValue] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cost] [money]
NULL CONSTRAINT [d_Cost18] DEFAULT ((0)),
[UOM] [varchar]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChoiceName] [varchar]
(200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ProductOptCh] PRIMARY KEY CLUSTERED
(
[OptionCD]
ASC,
[CompanyID]
ASC,
[ProductID]
ASC,
[ChoiceCD]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF
GO
ALTER
TABLE [dbo].[ProductOptCh] WITH CHECK ADD CONSTRAINT [FK_ProductOptCh_ProductOpt] FOREIGN KEY([OptionCD], [CompanyID], [ProductID])
REFERENCES
[dbo].[ProductOpt] ([OptionCD], [CompanyID], [ProductID])
GO
ALTER
TABLE [dbo].[ProductOptCh] CHECK CONSTRAINT [FK_ProductOptCh_ProductOpt]
GO
ALTER
TABLE [dbo].[ProductOptCh] WITH CHECK ADD CONSTRAINT [is_ch_measure_for] FOREIGN KEY([UOM])
REFERENCES
[dbo].[UOM] ([UOM])
ON
UPDATE CASCADE
ON
DELETE CASCADE
GO
ALTER
TABLE [dbo].[ProductOptCh] CHECK CONSTRAINT [is_ch_measure_for]
GO
ALTER
TABLE [dbo].[ProductOptCh] WITH CHECK ADD CONSTRAINT [CK_IsLockedOut6] CHECK (([IsLockedOut]=(0) OR [IsLockedOut]=(1)))
GO
ALTER
TABLE [dbo].[ProductOptCh] CHECK CONSTRAINT [CK_IsLockedOut6]
===========================================================================
GO
/****** Object: Table [dbo].[UOM] Script Date: 06/05/2006 17:34:51 ******/
SET
ANSI_NULLS OFF
GO
SET
QUOTED_IDENTIFIER ON
GO
SET
ANSI_PADDING OFF
GO
CREATE
TABLE [dbo].[UOM](
[UOM] [varchar]
(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Descr] [varchar]
(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [XPKUOM] PRIMARY KEY CLUSTERED
(
[UOM]
ASC
)
WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET
ANSI_PADDING OFF

Dam again!

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply