Random Competition Draws

Database

Random Competition Draws

Hi,

Hoping someone can assist.

We often get requests to do competition draws. A vendor supplies the data and we need to randomly draw a winner using Teradata. We do get audited on our competition draws so the draw needs to be truely random.

Unfortunately often our data is recieved as below example;

MSN;Number_of_Entries
0401000000;25
0412121212;13
0423568541;50 etc

We need to give each entity the number of chances to win as in cloumn 2.

How would you randomly pick a winner giving each entrant the correct number of chances to win?

These files are often extremely large.

Thanks
Magicbananas
2 REPLIES
Teradata Employee

Re: Random Competition Draws

Hello,

One of the easiest solutions may be as follows:

- Create another table with two columns, one auto-generated identity, other MSN value.
- Insert "Number_of_Entries" times the MSN value in the newly created table.
- Select the winner using RAND, ROW_NUMBER or RANK function (on auto-generated column).

HTH.

Regards,

Adeel
Enthusiast

Re: Random Competition Draws

You can use recursion (if there's no max limit) or a lookup table if there's a max limit on the number of entries (even calendar would do).