0

Please login or register to vote for this query.

(click on this box to dismiss)

http://stackoverflow.com/questions/6855895/drawing-a-raffle-ticket-winner-in-t-sql/6855993#6855993

Q&A about the site for people looking to improve their interpersonal communication skills

DECLARE @TicketHolder TABLE
(
    [Name] VARCHAR(100),
    Tickets DECIMAL(18, 8)
);

INSERT INTO @TicketHolder ([Name], Tickets)
SELECT 'Bob', 10.37
UNION ALL
SELECT 'Lisa', 1
UNION ALL
SELECT 'Robbie', 150.981
UNION ALL
SELECT 'Cathy', 97.888531;

declare @rnd float
set @rnd = rand() * (select sum(tickets) from @TicketHolder)

select  Name
,    StartingNumber
,    EndingNumber
,    @rnd as DrawNumber
,    case when StartingNumber <= @rnd and @rnd < EndingNumber then 'Winner' else '' end
from  (
    select  t1.Name
    ,    IsNull((
        select  sum(Tickets)
        from  @TicketHolder t2
        where  t2.Name < t1.Name
        ),0) as StartingNumber
    ,    IsNull((
        select  sum(Tickets)
        from  @TicketHolder t2
        where  t2.Name <= t1.Name
        ),0) as EndingNumber
    from  @TicketHolder t1
    ) as running_sum
order by
    Name

fork query

created jul 28 11 Andomar