Reverse recursion for some specific statistical calculation

Question

Reverse recursion for some specific statistical calculation

Dennes Spek

SSC Enthusiast

Points: 119
More actions
October 2, 2014 at 9:03 am

#397444

Hi All,
First of all, I couldn't come up with a better title explaining my t-sql challenge. Sorry 🙂
I have been trying to wrap my head around this for days.
I have a table :
id + A + B
1 |0,11| 0
2 |0,45| 0,5
3 |0,85| 0,75
I need to calculate the following :
F = 0,85 * ( 1 - 0,75 * ( 1 - 0,45 * ( 1 - 0,5 * ( 1 - 0,11 * ( 1 - 0 )))))
In which F = A3 * ( 1 - B3 * ( 1 - A2 * ( 1 - B2 * ( 1 - A1 * ( 1 - B1 )))))
It seemed quite easy at first glance.
Is there any other way perhaps. Just a suggestion would do, as I can understand this not a free consultancy forum :).
I Built it up via string concatenation and thought to execute the dynamic sql with sp_exec and get the result.
As I don't like dynamic sql I was wondering If there is any other way..
ALTER PROC [dbo].[Tools_Serial_FE]
AS
BEGIN
DECLARE @IP FLOAT,
@IF FLOAT,
@FE FLOAT,
@id INT,
@end INT,
@iterator INT,
@MathSequence VARCHAR(4000)
DECLARE efcursor CURSOR FOR SELECT id,127.0.0.1,[IF] FROM TEST.ff ORDER BY id DESC-- id,IP,IF,(FE)
OPEN efcursor
SET @iterator = 0
SET @MathSequence = 'SELECT ROUND( ('
SELECT @end = COUNT(id) FROM TEST.ff
WHILE @end > @iterator
BEGIN
FETCH NEXT FROM efcursor INTO @id,@IP,@IF
SET @MathSequence = @MathSequence + CAST(@IP AS VARCHAR(255)) + ' * ( 1 - ' + CAST(@IF AS VARCHAR(255)) + ' * ( 1 - '
SET @iterator += 1
END
SELECT @MathSequence = SUBSTRING(@MathSequence,1, (len(@MathSequence) - 8) ) + REPLICATE(')',(@iterator*2)-1) + ' ) , 2 ) AS FE'
PRINT @MathSequence
PRINT '...retrieve calculation via sp_exec ...'
CLOSE efcursor
DEALLOCATE efcursor
END
Any suggestion would be greatly appreciated.
Kind Regards,
Dennes

Viewing 6 posts - 1 through 5 (of 5 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Hi, welcome to the forums.

It's considered polite to post the sample data as DDL and insert statements as well as the expected output based on the sample data. This time, I did it for you and included a possible solution.

I hope that it can give you an idea on how to do this without a cursor.

CREATE TABLE SampleData(

id int PRIMARY KEY CLUSTERED,

A decimal(18,12),

B decimal(18,12))

INSERT INTO SampleData VALUES

(1 ,0.11, 0),

(2 ,0.45, 0.5),

(3 ,0.85, 0.75)

DECLARE @F decimal(18, 12) = 1,

@F2 decimal(18, 12) = 1;

SELECT @F = CASE WHEN b.id IS NULL THEN (a.A*((1-a.B)*@F))

ELSE ((1-a.A)*((1-a.B)*@F)) END

FROM SampleData a

LEFT JOIN SampleData b ON a.id = b.id - 1

ORDER BY a.id

SELECT @F

SELECT 0.85 * ( (1 - 0.75) * ( (1 - 0.45) * ( (1 - 0.5) * (( 1 - 0.11) * (( 1 - 0 ))))))

GO

DROP TABLE SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

;WITH SampleData AS (

SELECT * FROM (VALUES (1 ,0.11, 0),(2 ,0.45, 0.5),(3 ,0.85, 0.75)) d (id, A, B)

)

SELECT *, F = r3.A * ( 1 - r3.B * ( 1 - r2.A * ( 1 - r2.B * ( 1 - r1.A * ( 1 - r1.B )))))

FROM SampleData r3

LEFT JOIN SampleData r2 ON r2.id+1 = r3.id

LEFT JOIN SampleData r1 ON r1.id+2 = r3.id

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Assuming you need this to be able to truly recurse through n number of levels - here's an actual recursive version of it:

WITH testdata as (

select 1 as ID, 0.11 as A, cast(0 as float(53)) as B union all

select 2 as ID, 0.45 as A, 0.5 as B union all

select 3 as ID, 0.85 as A, 0.75 as B

),

recurseCTE as (

select id,a,b,1-a*(1-b) as interim

from testdata where ID=1

UNION ALL

select T.id,T.a,T.b,1-T.a*(1-T.b*r.interim) interim

from testdata T join RecurseCTE R on T.id=R.id+1

)

select *, 1-interim as F

from recurseCTE

where ID=(select max(ID) from testdata)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Dennes Spek SSC Enthusiast Points: 119 More actions · Answer 4

Hi Luis,

Thank you very much for your thinking alongside!

Based on your solution the two other repliers solved my problem completely.

Next time ( hope not 🙂 ) I'll put complete cut and paste sql. I did not realize people

would actually take the time to write whole sql.

Thanks again!

Dennes

Dennes Spek SSC Enthusiast Points: 119 More actions · Answer 5

Ah,

I thought replying to a post would make it a child of the original.

So a proper thank you for ChrisM@Work and offcourse

Matt Miller who solved my problem.

Now I can continue my application in a satisfying way.

I'll post my complete solution when I'm done, so others can benefit too.

Cheers!!

Dennes