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 3 years, 8 months 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.
Helpful Links:
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 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 MyTableALTERNATELY, 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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
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;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
-
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.
-
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.
Helpful Links:
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.
Helpful Links:
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
;
GOIf 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.
Helpful Links:
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.
Please post DDL and follow ANSI/ISO standards when asking for help.
- 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.
Helpful Links:
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?
Please post DDL and follow ANSI/ISO standards when asking for help.
- 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 15 total)
You must be logged in to reply to this topic. Login to reply