mardi 22 août 2017

Can you use random numbers in a CASE statement (SQL Server 2016)

I have tried to construct a CASE statement that will fit into a larger SELECT. I want each row to resolve separately (using a different random number per row) but for the random number to be the same when evaluating the case statement within a particular row if that makes sense.

I have tried

    SELECT 
    [Player name]
    ,[Stake]
    ,[current jackpot]

         ,CASE

         WHEN 
         rand() < 0.23
         THEN
         'Win'
         WHEN
         rand() BETWEEN 0.23 AND 0.89
         then
         'Lose'
         when
         rand() >= 0.89
         then
         'Jackpot'
         else
         'other'
         end as [outcome]
...

but the fact that I sometimes get an 'other' result tells me that each WHEN is creating a different random number to evaluate. I also can't declare a global random number at the start and use that because each line should resolve separately.




Aucun commentaire:

Enregistrer un commentaire