# How to calculate a column recursively ?

• 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 9 months, 1 week ago by  FKBEYS.
• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• 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 Result3From 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 Result3From 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.

• 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 price3from  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;`

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

• 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 9 months, 1 week ago by  FKBEYS.
• 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)`
• Mr Steve, thank you for your help. it works great.

• 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 9 months, 1 week ago by  FKBEYS.
• This reply was modified 9 months, 1 week ago by  FKBEYS.
• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

• 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".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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