How to calculate a column recursively ?

  • FKBEYS

    SSC Veteran

    Points: 271

    i have a data as below.

    Price = 25.0

    Discount1=10 percentage

    Discount2=20 percentage

    Discount3=30 percentage

    i want to calculate the price's discounts in sequence Discount1,Discount2 and Discount3 respectively.

    Query= Select result1=Price-Price*Discount1/100.0,result2=result1-result1*Discount2/100.0,result3=result2-result2*Discount3/100.0 From MyTable.

    how can i do it? i searched it but i couldnt find any answer. if the question is asked before, please delete this and redirect me the answer. thank you.

     

    • This topic was modified 1 week, 5 days ago by  FKBEYS.
  • Jeff Moden

    SSC Guru

    Points: 996847

    Change all the /100 things to /100.0

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    FKBEYS - I think you are thinking about the calculation wrong or I may be looking at it wrong.  If I understand right, your table contains the columns "Price", "Discount1","Discount2","Discount3", right?  If so, your query:

    SELECT Price-Price*Discount1/100.0 AS Result1
    ,result1-result1*Discount2/100.0 AS Result2
    ,result2-result2*Discount3/100.0 AS Result3
    From MyTable

    (rewritten as I think it is a bit easier to see what is happening this way) fails because there is no column "result1" or "result2" in MyTable, right?

    Looking at the above, it is easy to see what is wrong though - result1 is a column on your FINAL table, not on your source table.  The easiest way to get that to be a cumulative discount is to copy-paste the previous value in or to have some intermediate tables/cte's holding the values for you.  My approach would be the following:

    SELECT Price-Price*Discount1/100.0 AS Result1
    ,(Price-Price*Discount1/100.0)-((Price-Price*Discount1/100.0)*Discount2/100.0) AS Result2
    ,(Price-Price*Discount1/100.0)-((Price-Price*Discount1/100.0)*Discount2/100.0)-((Price-Price*Discount1/100.0)-((Price-Price*Discount1/100.0)*Discount2/100.0))*Discount3/100.0 AS Result3
    From MyTable

    ALTERNATELY, you could do this with 2 CTE's  that calculate the result1 in CTE1, result2 in CTE2 and result3 in the final select statement.

    My GUESS with this question is Jeff replied and then the OP was changed as the OP already has Jeff's suggestion implemented.

  • Steve Collins

    SSC Eights!

    Points: 889

    You could successively cross apply

    declare
    @price int=100;

    ;with some_discounts_cte(first_pct, second_pct, third_pct) as (
    select 20.0, 20.0, 50.0)
    select
    @price orig_price,
    first_disc.price price1,
    second_disc.price price2,
    third_disc.price price3
    from
    some_discounts_cte sdc
    cross apply
    (select @price*(1-sdc.first_pct/100) price) first_disc
    cross apply
    (select first_disc.price*(1-sdc.second_pct/100) price) second_disc
    cross apply
    (select second_disc.price*(1-sdc.third_pct/100) price) third_disc;
  • FKBEYS

    SSC Veteran

    Points: 271

    Thank you for your help Mr. Brian

    there are 10 discounts. So it would be hard to write a function. so that i m trying to find a way to do it recursively.

    With cte how can i do it? i m not so good on Sql

    • This reply was modified 1 week, 5 days ago by  FKBEYS.
  • Jonathan AC Roberts

    SSCoach

    Points: 17321

    If you want to apply discounts to discounts you can do it with CROSS APPLY

    Select x.Price,
    A.Result1,
    B.Result2,
    C.Result3
    From MyTable x
    cross apply(values (x.Price*(1-x.Discount1)/100)) A(Result1)
    cross apply(values (A.Result1*(1-x.Discount2)/100)) B(Result2)
    cross apply(values (B.Result2*(1-x.Discount3)/100)) C(Result3)
  • FKBEYS

    SSC Veteran

    Points: 271

    Mr Steve, thank you for your help. it works great.

  • FKBEYS

    SSC Veteran

    Points: 271

    it works sir.the most important thing is, i got the idea of Cross apply.

    Thank you sir.

    I m just curious to know, in the future, the table will have more then 2 million records .

    we will see, weather the Cross Apply function will reduce the performance or not. If it reduce the performance, i will write all equations by hand.

    Or maybe there is a mathematical function to calculate this.

    • This reply was modified 1 week, 5 days ago by  FKBEYS.
    • This reply was modified 1 week, 5 days ago by  FKBEYS.
  • Jeff Moden

    SSC Guru

    Points: 996847

    Sorry... post deleted.  Looking at it all again.

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996847

    Mr. Brian Gale wrote:

    My GUESS with this question is Jeff replied and then the OP was changed as the OP already has Jeff's suggestion implemented.

    That would be a good guess, Brian.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Jeff Moden

    SSC Guru

    Points: 996847

    Ok... my apologies for not reading the original post well enough.  My only concern was the /100 thing.  Now I understand what's being done.

    The CROSS APPLY method works just fine but, as they say, "In the interest of science", let's see how to do it another way. 😀

    If we look back at Steve Collins' first post on this thread, he divides the discount by 100 and subtracts that from 1 (which is 100%) to come up with a fractional multiplier that he multiplies times the price.  He then cascades that multiplier through the use of CROSS APPLYs creating a "running product".

    Of course, SQL Server has things like SUM() OVER() but it does not have a PROD() OVER() to create the "running product" with, but the math to create our own is simple... if you take the LOG of a value and add it to the LOG of another value and then take the anti-log (EXP) of that, you'll find out the answer is the PRODUCT (of multiplication) as if you had multiplied the original two numbers.

    We know how to do running SUMs and, with the help of the LOG and EXP functions, it's rather easy to make a running product.

    Also, to keep from having to change code every time a discount changes and the fact that we may have more than 1 discount schedule to apply to different things based upon some group membership, here's how I'd make the DiscountSchedule table.  The data I made for it has discount schedule "1" with the OP's original discounts and discount schedule "2" as the discounts that Steve Collins used in his good code.  I also added two more schedules just to demonstrate the flexibility of it all.

    --===== Create and populate a test DiscountSchedule table to hold
    -- Different discount "schedules".
    -- DROP TABLE IF EXISTS dbo.DiscountSchedule --Commented out for safety.
    ;
    CREATE TABLE dbo.DiscountSchedule
    (
    DiscountScheduleID SMALLINT
    ,DiscountLevel TINYINT
    ,DiscountPct TINYINT
    ,CONSTRAINT PK_DiscountGroup
    PRIMARY KEY CLUSTERED (DiscountScheduleID,DiscountLevel)
    )
    ;
    INSERT INTO dbo.DiscountSchedule WITH (TABLOCK)
    (DiscountScheduleID, DiscountLevel,DiscountPct)
    VALUES --Discount Schedule 1
    (1,1,10)
    ,(1,2,20)
    ,(1,3,30)
    --Discount Schedule 2
    ,(2,1,20)
    ,(2,2,20)
    ,(2,3,50)
    --Discount Schedule 3
    ,(3,1,50)
    --Discount Schedule 4
    ,(4,1,10)
    ,(4,2, 5)
    ,(4,3, 5)
    ,(4,4, 5)
    ;
    --===== Let's see what we've got
    SELECT *
    FROM dbo.DiscountSchedule
    ORDER BY DiscountScheduleID, DiscountLevel
    ;

    ... and that returns the following...

    Yeah... I know... nothing thrilling yet...

    But here's how you can create the running product multiplier from the DiscountPct mathematically using the sum of the logs to create a PRODUCT instead of using the CROSS APPLY's, which would work really well for varying numbers of discount levels per discount schedule.

    The really cool part is... NOW we can put it into an iTVF!

     CREATE FUNCTION dbo.DiscountMultiplier
    /**********************************************************************************************************************
    Purpose:
    Given a DiscountScheduleID, return the discount schedule and the multiplier as a "product aggregate" for each discount
    level regarless how many or how few levels of discount there are.
    -----------------------------------------------------------------------------------------------------------------------
    Example Usage:
    DECLARE @DiscountSchedule SMALLINT
    SELECT tt.Price
    ,dm.*
    ,DiscountPriceByLevel = dm.DiscountMultiplier*tt.Price
    FROM (VALUES (1,25),(2,25),(3,50),(4,100))tt(DiscountSchedule,Price) --Using this as a test table of prices
    CROSS APPLY dbo.DiscountMultiplier(tt.DiscountSchedule) dm
    ;
    -----------------------------------------------------------------------------------------------------------------------
    Reference:
    https://www.sqlservercentral.com/forums/topic/how-to-calculate-a-column-recursively#post-3776132
    -----------------------------------------------------------------------------------------------------------------------
    Revision History:
    Rev 00 - 30 Jul 2020 - Jeff Moden
    - Initial creation and unit test.
    **********************************************************************************************************************/
    --===== Define the I/O for this function
    (@pDiscountScheduleID SMALLINT)
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN
    --===== Create the "Running Product" of multipliers based on 100-DiscountPct for the given discount schedule.
    SELECT DiscountScheduleID, DiscountLevel, DiscountPct
    ,DiscountMultiplier = EXP(SUM(LOG(1.0-(DiscountPct/100.0)))
    OVER (ORDER BY DiscountLevel ROWS UNBOUNDED PRECEDING))
    FROM dbo.DiscountSchedule
    WHERE DiscountScheduleID = @pDiscountScheduleID
    ;
    GO

    If you run the code from the example usage located in the flower box of that function, you'll get the following as a result (provided that you put the dbo.DiscountSchedule table and data in place)...

    That would also lend itself well to some dynamic SQL to CROSS TAB (pivot) the result, which I don't have time for tonight and will leave that up to the ultimate user(s) for now. 😉

    I'll also leave the final rounding up to you.

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • jcelko212 32090

    SSCrazy Eights

    Points: 9021

    I have a data as below.

    Why did you fail to post any DDL? But more than that, why do you think that SQL and the relational model use any kind of sequence? A table is a set of columns, which come into existence and have values all at once. Columns are not computed from left to right, like fields inrecords, and an old file system. That is the nature of sets. You're missing some fundamental mathematical principles; have you ever had a course in set theory?

    CREATE TABLE Catalog

    (product_gtin CHAR(15) NOT NULL PRIMARY KEY,

    product_price DECIMAL (8,2) NOT NULL

    CHECK (product_price >= 0.00),

    lvl_1_discount_price AS product_price * 0.90,

    lvl_2_discount_price AS (product_price * 0.90) * 0.80,

    lvl_3_discount_price AS ((product_price * 0.90) * 0.80) *0.70);

    >> I want to calculate the price's discount_rates in sequence discount_rate1, discount_rate2 and discount_rate3 respectively. <<

    I'm willing to bet that you did not post DDL because you don't understand what a table is and why it has have a PRIMARY KEY by definition. I've done this with computed columns by simply expanding the computations. You seem to be looking for recursion or iteration, both of which are procedural programming methods. You don't seem to understand that SQL is declarative.

    I want you to carefully look at and understand this chunk of pseudo-SQL:

    SELECT a AS (b * c), b AS (a * c), c AS ( a * b)

    FROM Foobar;

    Fill in some integer values for a, b and c. Now try to make it work when you don't have the ability to scan this expression left to right. It's a total mess! Circular self-references are a major logical problem.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeff Moden

    SSC Guru

    Points: 996847

    jcelko212 32090 wrote:

    I have a data as below.

    Why did you fail to post any DDL? But more than that, why do you think that SQL and the relational model use any kind of sequence? A table is a set of columns, which come into existence and have values all at once. Columns are not computed from left to right, like fields inrecords, and an old file system. That is the nature of sets. You're missing some fundamental mathematical principles; have you ever had a course in set theory?

    CREATE TABLE Catalog

    (product_gtin CHAR(15) NOT NULL PRIMARY KEY,

    product_price DECIMAL (8,2) NOT NULL

    CHECK (product_price >= 0.00),

    lvl_1_discount_price AS product_price * 0.90,

    lvl_2_discount_price AS (product_price * 0.90) * 0.80,

    lvl_3_discount_price AS ((product_price * 0.90) * 0.80) *0.70);

    >> I want to calculate the price's discount_rates in sequence discount_rate1, discount_rate2 and discount_rate3 respectively. <<

    I'm willing to bet that you did not post DDL because you don't understand what a table is and why it has have a PRIMARY KEY by definition. I've done this with computed columns by simply expanding the computations. You seem to be looking for recursion or iteration, both of which are procedural programming methods. You don't seem to understand that SQL is declarative.

    I want you to carefully look at and understand this chunk of pseudo-SQL:

    SELECT a AS (b * c), b AS (a * c), c AS ( a * b)

    FROM Foobar;

    Fill in some integer values for a, b and c. Now try to make it work when you don't have the ability to scan this expression left to right. It's a total mess! Circular self-references are a major logical problem.

    The key to this problem is to avoid all the hard coding that you have not.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • jcelko212 32090

    SSCrazy Eights

    Points: 9021

    Discount rates don't usually change that much. They are usually mandated by law or company policy. The most dynamic data element in this table is going to be the prices! Avoiding recursion and cross applies should certainly improve performance, as well as readability. If there is a need to change a rate, by writing it is an expanded polynomial instead of doing the math, the source code can be quickly edited in the optimizer can handle the computations.

    I think people are making this problem much more complicated than it needs to be simply because of the way the poster worded it. He still stuck thinking in terms of scanning code left to right sequentially. But were dealing with slowly changing, if not constant, values here! You wouldn't consider writing out pi as a recursive series of some sort, would you?

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jonathan AC Roberts

    SSCoach

    Points: 17321

    jcelko212 32090 wrote:

    Discount rates don't usually change that much. They are usually mandated by law or company policy. The most dynamic data element in this table is going to be the prices! Avoiding recursion and cross applies should certainly improve performance, as well as readability. If there is a need to change a rate, by writing it is an expanded polynomial instead of doing the math, the source code can be quickly edited in the optimizer can handle the computations.

    I think internally the SQL compiler does expand out multiple cross applies. So it wouldn't/shouldn't make any noticeable difference to the speed of calculation/execution.

Viewing 15 posts - 1 through 15 (of 16 total)

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