SQL User Defined Function with Random Numbers

Question

I hope someone can help me.

I an trying to use an SQL user defined function on MS server 2008 to do the following

I am trying to predict when a customer will leave the bank, and I am doing this with series of coin-tosses until you leave. The coin-tosses do not necessarily are always at 50/50, so I can pass the probability to the function as a parameter as well as the current customer tenure.

The problem is that it is not allowed to use RAND() inside the function, I do not understand why but that is fine,there must be a reason.

here is my function:

create function NewTenure (@tenure integer, @p float) 
returns integer
as
begin
    declare @r float
    declare @newten int
    declare @attrite binary
    set @attrite=0
    set @newten = @tenure
    set @r = RAND()
    while (@attrite = 0)
    begin
        if (@r <= @p) set @attrite = 1;
        if (@r > @p) set @newten = @newten+1    
    end
    return(@newten)
end

As I see it, I need to generate a random value between 0-1 so I can compare it to the probability to determine if the customer left or not. If RAND() is not allowed, is there an SQL trick to generate it inside the function? (I know from school many years ago that I can write my own random number generator, but I do not want to do that, nor I believe I should have to do that, a random number is a basic computer function that one should expect - any other language I have ever used had one)

I plan to the call the function in a new query to update a temp table with the predicted tenure when they leave, which will then be used for other complex calculations.

Aaron Bertrand · Accepted Answer · 2012-08-22 19:49:36Z

9

This is a dirty trick.

CREATE VIEW dbo.vRand
AS
  SELECT r = RAND();
GO

CREATE FUNCTION dbo.fRand()
RETURNS DECIMAL(10,9)
AS
BEGIN
    RETURN (SELECT r FROM dbo.vRand);
END
GO

SELECT name, dbo.fRand()
  FROM sys.all_objects;

answered Aug 22, 2012 at 19:49

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL User Defined Function with Random Numbers

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related