February 15, 2009 at 2:15 pm
For 90% of the products in this products table that I am working on is all base on width and height.
It is possible to set a base cost for the product.
I have attached a snapshot of a model for this that I have start on just to help others help me figure out how to get this going.
I have actually asked this question many times before in many different formats and have not been able to fully wrap my arms around this.
I believe with a little help this time I can grasp this a lot better.
For the most part I need someone who has done this before to say yes, no, try this ect...
So i need a solution so that i can add a percentage to a base cost of a product.
door door Every 1' over 3'
$500.00 + ($500.00 x .40% ) = $700.00
Every product is base + width over. AND some or even base + width over + height over
If someone has someone similar to this a script to create the tables would be awesome as well 😉
Let me know what other information that I can give to help...
Dam again!
February 15, 2009 at 7:38 pm
So... are you saying a...
3' door is $500
4' door is $700
5' door is $900
6' door is $1100
???
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2009 at 8:09 pm
Yes.....
Now if it is blinds they are priced by width and height, "a grid"
I have attached an example grid that is used by the blind manufacturers and all blind companies online.
Thanks a lot Jeff!
Erik
Dam again!
February 16, 2009 at 7:15 am
For the initial posting you can try
select size, baseprice,
Case when size<=3 then baseprice
else baseprice + (baseprice*(size-3)*0.40)
end as newprice
from yourtables
Toni
February 16, 2009 at 8:30 am
Here is a solution with test data for your original grid of increase base on one set of units changing.
declare @product table (prodid int identity(1,1), prodname varchar(8), cost decimal(5,2))
declare @prodmeasure table (prodid int, adder decimal(5,2), basemeasure int, overunits int)
insert into @product
select 'Blinds',10 union all
select 'Doors',20 union all
select 'Boards',30
insert into @prodmeasure
select 1,.30,30,1 union all
select 2, .40,100,2 union all
select 3, .11,8,1
select * from @product
select * from @prodmeasure
declare @orderstbl table (orderid int identity(1,1),prodid int, size int)
insert into @orderstbl
select 1,30 union all
select 2, 100 union all
select 3, 8 union all
select 1,40 union all
select 2, 120 union all
select 3, 10
select * from @orderstbl
select orderid, size, cost, case when size <= basemeasure then cost
else cost + (cost *
cast((size - basemeasure)/overunits as decimal(5,2))
* adder)
end as finalprice
from @product pr
join @prodmeasure pm on pr.prodid = pm.prodid
join @orderstbl ot on ot.prodid = pm.prodid
order by orderid
To handle an additional dimension as in your second grid, you would change the final price calculation something along the lines of:
Case When size1 <= dimension1 AND size2 <= dimension2 then cost
else cost +(Case -- adder for dimension1
when size1 > dimesion1 (original else clause using size1) else 0 end )
+
(Case -- adder for dimension2
when size2 > dimesion2 (original else clause using size2) else 0 end)
End
I did not test the above specifically since there was no test data and frankly I did not feel like I should have to create any more for you. Next time if you provide test table definitions and a script to load test data for all your examples, it would be a lot easier to provide an answer.
Toni
February 16, 2009 at 9:41 am
Thank you Toni...
I would of posted a lot more; however, on this subject it is all blurry to me. On other types of schema like memberships, and simple products where the product cost is in the product table and that is the final cost i have better understanding of that.
On this one anything that I would have posted you would not have been able to work with it..
Thanks for the code I am going to dig into that now..
Erik
Dam again!
February 16, 2009 at 10:26 am
AFCC Inc. Com (2/15/2009)
Yes.....Now if it is blinds they are priced by width and height, "a grid"
I have attached an example grid that is used by the blind manufacturers and all blind companies online.
Thanks a lot Jeff!
Erik
I'd start by normalizing that grid... it should have 3 columns... "WidthTo", "LengthTo", and "Price". Clustered Primary key should be on the first two columns. Then, you'd simply select the "MIN" where each measurement was greater than the desired measurements to isolate the correct price. You could make it even easier by including "WidthFrom" and "LengthFrom" to make lookups easier.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2009 at 10:42 am
Ok, School session is open..... 😉
select orderid, size, cost, case when size <= basemeasure then cost
else cost + (cost *
cast((size - basemeasure)/overunits as decimal(5,2))
* adder)
end as finalprice
Can you please put this into words:
((size - basemeasure)/overunits as decimal(5,2)
--I am OK up to the overunits part
Cost = Cost + ((size requested by the shopper - the basemeasurement that represents the base cost for the product))/ ----WHAT is going on here with the over units ????-----
Thanks for being patient... 😉
Erik
Dam again!
February 16, 2009 at 10:49 am
AFCC Inc. Com (2/16/2009)
Ok, School session is open..... 😉select orderid, size, cost, case when size <= basemeasure then cost
else cost + (cost *
cast((size - basemeasure)/overunits as decimal(5,2))
* adder)
end as finalprice
Can you please put this into words:
((size - basemeasure)/overunits as decimal(5,2)
--I am OK up to the overunits part
Cost = Cost + ((size requested by the shopper - the basemeasurement that represents the base cost for the product))/ ----WHAT is going on here with the over units ????-----
Thanks for being patient... 😉
Erik
I'm not sure to whom your question is addressed. If it's in reference to my post about normalizing the cost table you posted as a gif, if you could provide the data in a readily consumable format (see the link in my signature below for the best way to do that), then I might be able to help a bit more.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2009 at 10:57 am
Now that I've looked at the second grid, that sure adds a bunch of complexity as there does not seem to be a straightforward algorithm for the pricing. So the algorithm I posted would not help you there. A table lookup as Jeff posted would seem the only way to get the results unless you can figure a consistent formula to apply.
That being said...
Can you please put this into words:
((size - basemeasure)/overunits as decimal(5,2)
--I am OK up to the overunits part
Cost = Cost + ((size requested by the shopper - the basemeasurement that represents the base cost for the product))/ ----WHAT is going on here with the over units ????-----
Size-basemeasure is the actual size of the product minus the basic size which gives the overage.
dividing it by the overunits gives the additional overage adders based on what you are using as the overunits (for example one product could charge per each unit over the basic size while another might charge only for each two units over the basic size). The decimal(5,2) is 5 digits with 2 after the decimal point.
The specification of overage units could be overkill in which case, you would get rid of the divide by overunits bit and assume each product is based on exactly one additional cost per each additional unit.
BUT>>>> without a consistent algorithm that would apply to all cases, a lookup table is the way you need to go <<<< and you can forget the formulas entirely. Sorry if I added a layer of confusion.
Toni
February 16, 2009 at 11:01 am
Jeff, Erik was addressing the question to an earlier post of mine that addressed only the original question and did not address the added complexity of the second grid which your post does address.
In fact your post invalidates my solution as there is no consistent algorithm to be applied that I could discern.
Toni
February 16, 2009 at 11:05 am
Ah... got it. Thanks, Toni.
Erik... how 'bout it? Got data?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2009 at 11:12 am
I am making the tables right now....
I will post back shortly...
Thanks
Dam again!
February 16, 2009 at 11:46 am
----Database----------------------------------------------------
USE [master]
GO
/****** Object: Database [Testing] Script Date: 02/16/2009 12:38:19 ******/
CREATE DATABASE [Testing] ON PRIMARY
( NAME = N'Testing', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Testing.mdf' , SIZE = 6144KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Testing_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Testing_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Testing', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Testing].[dbo].[sp_fulltext_database] @action = 'disable'
end
====================================================
----Tables----------------------------------------------------
--===== If the test table already exists, drop it
IF OBJECT_ID('dbo.WidthFROMLengthFROM','U') IS NOT NULL
DROP TABLE dbo.WidthFROMLengthFROM
CREATE TABLE [dbo].[WidthFROMLengthFROM](
[WidthFrom] [int] NOT NULL,
[LengthFrom] [int] NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK_WidthFROMLengthFROM] PRIMARY KEY CLUSTERED
(
[WidthFrom] ASC,
[LengthFrom] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--===== If the test table already exists, drop it
IF OBJECT_ID('dbo.WidthTOLengthTO','U') IS NOT NULL
DROP TABLE dbo.[WidthTOLengthTO]
CREATE TABLE [dbo].[WidthTOLengthTO](
[WidthTO] [int] NOT NULL,
[LengthTO] [int] NOT NULL,
[Price] [money] NOT NULL,
CONSTRAINT [PK_WidthTOLengthTO] PRIMARY KEY CLUSTERED
(
[WidthTO] ASC,
[LengthTO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
----Data----------------------------------------------------
--FROM
INSERT INTO [Testing].[dbo].[WidthFROMLengthFROM]
([WidthFrom]
,[LengthFrom]
,[Price])
SELECT 12, 30, 95.00 union all
SELECT 12, 36,112.00 union all
SELECT 12, 42,118.00 union all
SELECT 12, 48,132.00 union all
SELECT 12, 54,142.00 union all
SELECT * FROM dbo.WidthFROMLengthFROM
----------------------------------------------------
GO
--TO
INSERT INTO [Testing].[dbo].[WidthTOLengthTO]
([WidthTO]
,[LengthTO]
,[Price])
SELECT 12, 30, 95.00 union all
SELECT 24, 30,80.00 union all
SELECT 28, 30,93.00 union all
SELECT 32, 30,107.00 union all
SELECT * FROM dbo.WidthTOLengthTO
This is where I really need help showing how to integrate the productID into this type of look up tables.
Thanks again
Dam again!
February 16, 2009 at 11:50 am
Ok now that I am looking at this ..
I have to remember that the price is computed from the .% in the length width columns. SO my posted schema is wrong.
I do not need to have any visible prices stored because all the prices are computed from the .% of the chosen width length..
THIS IS MADNESSSSSS....
Dam again!
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply