How to add cost percentage to product

  • 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!

  • So... are you saying a...

    3' door is $500

    4' door is $700

    5' door is $900

    6' door is $1100

    ???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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!

  • 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

  • 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

  • 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!

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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!

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

  • Ah... got it. Thanks, Toni.

    Erik... how 'bout it? Got data?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I am making the tables right now....

    I will post back shortly...

    Thanks

    Dam again!

  • ----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!

  • 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