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 for pro webmasters

-- Enter Query Title
-- Enter Query Description

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;

select  t1.Name
,    IsNull((
    select  sum(Tickets)
    from  @TicketHolder t2
    where  t2.Name < t1.Name
    ),0) as StartingNumber
from  @TicketHolder t1
order by 
    name

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

select  top 1 @rnd as DrawnNumber
,    t1.Name as Winner
from  @TicketHolder t1
where  (
    select  sum(Tickets)
    from  @TicketHolder t2
    where  t2.Name <= t1.Name
    ) > @rnd
order by
    t1.Name

fork query

created jul 28 11 Andomar