wafw1971 (2/20/2013)
I have just been told I have done it completely wrong by my boss and the query above hasn't randomised anything on our data, I have got 48000 records with a 2 night stay, 48000 records for a 27 night stay etc.What I need is 30% of the departure dates to be 2 days in length, 10% to be 3 Days and the rest to be randomised amongst 1,4 to 28.
Can anyone help?
Possibly. What you need is to generate random numbers based on a multinomial distribution. See the second article in my signature links (about random number generators in SQL) for a function that will do this.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St