TSQL Sweepstakes Script

Question

I need to run a sweepstakes script to get X amount of winners from a customers table. Each customer has N participations. The table looks like this

CUSTOMER-A 5

CUSTOMER-B 8

CUSTOMER-C 1

I can always script to have CUSTOMER-A,B and C inserted 5, 8 and 1 times respectively in a temp table and then select randomly using order by newid() but would like to know if there's a more elegant way to address this.

Answer 1

try this:

   Select Top X CustomerId
   From (Select CustomerId,
         Rand(CustomerId) *
            Count(*) / 
              (Select Count(*) 
               From Table) Sort
         From Table
         Group By CustomerId) Z
   Order By Sort Desc

EDIT: abovbe assumed multiple rows per customer, one row per participation... Sorry, following assumes one row per customer, with column Participations holding number of participations for that customer.

Select Top 23 CustomerId
From ( Select  CustomerId,
        Participations - RAND(CustomerId) * 
         (Select SUM(Participations ) From customers) sort
       from customers) Z
Order By sort  desc

Answer 2

(Update: Added final query.)
(Update2: Added single query to avoid temp table.)

Here's the hard part using a recursive CTE plus the final query that shows "place".

Code

DECLARE @cust TABLE (
    CustomerID      int     IDENTITY,
    ParticipationCt int
)

DECLARE @list TABLE (
    CustomerID      int,
    RowNumber       int
)

INSERT INTO @cust (ParticipationCt) VALUES (5)
INSERT INTO @cust (ParticipationCt) VALUES (8)
INSERT INTO @cust (ParticipationCt) VALUES (1)
INSERT INTO @cust (ParticipationCt) VALUES (3)
INSERT INTO @cust (ParticipationCt) VALUES (4)

SELECT * FROM @cust

;WITH t AS (
    SELECT
        lvl = 1,
        CustomerID,
        ParticipationCt
    FROM @Cust

    UNION ALL

    SELECT
        lvl = lvl + 1,
        CustomerID,
        ParticipationCt
    FROM t
    WHERE lvl < ParticipationCt
)
INSERT INTO @list (CustomerID, RowNumber)
SELECT
    CustomerID,
    ROW_NUMBER() OVER (ORDER BY NEWID())
FROM t

--<< All rows
SELECT * FROM @list ORDER BY RowNumber

--<< All customers by "place"
SELECT
    CustomerID,
    ROW_NUMBER() OVER (ORDER BY MIN(RowNumber)) AS Place
FROM @list
GROUP BY CustomerID

Results

 CustomerID ParticipationCt
----------- ---------------
          1               5
          2               8
          3               1
          4               3
          5               4

 CustomerID   RowNumber
----------- -----------
          4           1
          1           2
          1           3
          2           4
          1           5
          5           6
          2           7
          2           8
          4           9
          2          10
          2          11
          2          12
          1          13
          5          14
          5          15
          3          16
          5          17
          1          18
          2          19
          2          20
          4          21

 CustomerID Place
----------- -----
          4     1
          1     2
          2     3
          5     4
          3     5

---

Single Query with No Temp Table

It is possible to get the answer with a single query that does not use a temp table. This works fine, but I personally like the temp table version better so you can validate the interim results.

Code (Single Query)

;WITH List AS (
    SELECT
        lvl = 1,
        CustomerID,
        ParticipationCt
    FROM @Cust

    UNION ALL

    SELECT
        lvl = lvl + 1,
        CustomerID,
        ParticipationCt
    FROM List
    WHERE lvl < ParticipationCt
),
RandomOrder AS (
    SELECT
        CustomerID,
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowNumber
    FROM List
)
SELECT
    CustomerID,
    ROW_NUMBER() OVER (ORDER BY MIN(RowNumber)) AS Place
FROM RandomOrder
GROUP BY CustomerID