wafw1971 (2/21/2013)
Hi Dwain,I didn't mean to offend you, if that's the case I am sorry. My boss helped me with that query, is it not right?
1.35 Million Records of which
30% should 2 night stays
20% should be 3 night stays
and the other 50% to be randomised between 1, 4 and 28 days.
I do have another question if you could help in the same vain.
I now need to make 15% of them cancelled by inserting a random Cancelled Date. However the cancelled date must be =>Booking Date and <=Arrival Date.
I have completed the random section but I now need to know how to add greater than and less than part to the query:
Can you help?
SELECT ArrivalDate,
DATEADD(day,
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.85 THEN NULL ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.85 and 0.88 THEN 0 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.88 and 0.92 THEN -1 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.97 THEN -7 ELSE
Round(Rand(CHECKSUM(NEWID())) * -90,0) END END END END,ArrivalDate) AS DaystoReduce
FROM Bookings
Thanks
Wayne
No offense taken. I was referring to this line:
Round(Rand(CHECKSUM(NEWID())) * 28,0) END END,ArrivalDate)
Which I believe will throw some 0s, 2s and 3s into the mix.
I know a way to generate 15% random cancellations as you say you need, however to do it I need you to provide me with some DDL for the table and some test data in consumable form. It is not something I can just write up without testing and expect to get it right.
The way I do it might be easier or harder depending on what key fields are available to work with (e.g., if a unique booking number is present).
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