I can generate random numbers just fine using NewID(). I got it from SqlAuthority.com, works a charm.
I am generating numbers between 1 and 5. If I just use NewID() or similar, I will likely get an even spread of values, which I don't want. Instead, I wanted the frequency to decrease as the number increased. So for example, I would have a spread like this (for example):
(1 indicates a mild symptom, 5 indicates a fatality)
1 - 40%
2 - 25%
3 - 20%
4 - 14%
5 - 1%
I was playing around with generating numbers and using something like 6-(Power(randomNum,CAST(1/6) AS DECIMAL())
and then using FLOOR or CEILING to round up or down as necessary. The catch is that I'm trying to avoid using a cursor for all of the data generation. I may have to for the patients, and then loop until the code generates a grade 5, because that indicates a fatality. At that point, I would "deactivate" the patient so no more cycles would be run.
Is this a sane way to do this, or am I missing something obvious? (Sorry, I would read Ken Henderson's advice in Guru's Guide, but it's at home and I'm not.)