Ok, so what you're bascally doing is generating a table with sample-data, is that it? so it doesn't really matter which bookings are cancelled as long as you cancel according to the specifications? In that case my understanding is this: 2% of the total bookings (which is the equivaent of 20% of 8%) are cancelled on the same day as the Arrival Date, 2% are cancelled the day before the Arrival Date, etc... (the NewID()-stuff will totally kill performance):
Prerequisite: a UDF that generates a random integer between two parameters
--> Same day cancellations
;WITH cte AS (
SELECT TOP 2 PERCENT Booking_Skey, ArrivalDate
FROM Bookings
WHERE DATEPART(Year,ArrivalDate) = '2010'
AND CancelledDate IS NULL
ORDER BY NewID()
)
UPDATE cte SET CancelledDate = ArrivalDate
--> Day before cancellations
;WITH cte AS (
SELECT TOP 2 PERCENT Booking_Skey, ArrivalDate
FROM Bookings
WHERE DATEPART(Year,ArrivalDate) = '2010'
AND CancelledDate IS NULL
ORDER BY NewID()
)
UPDATE cte SET CancelledDate = ArrivalDate-1
--> Within a week before cancellations
;WITH cte AS (
SELECT TOP 2 PERCENT Booking_Skey, ArrivalDate
FROM Bookings
WHERE DATEPART(Year,ArrivalDate) = '2010'
AND CancelledDate IS NULL
ORDER BY NewID()
)
UPDATE cte SET CancelledDate = (ArrivalDate - dbo.Random(1, 7))
--> Within 3 months before cancellations
;WITH cte AS (
SELECT TOP 2 PERCENT Booking_Skey, ArrivalDate
FROM Bookings
WHERE DATEPART(Year,ArrivalDate) = '2010'
AND CancelledDate IS NULL
ORDER BY NewID()
)
UPDATE cte SET CancelledDate = (ArrivalDate - dbo.Random(1, 90))
- Lumbago
My blog-> http://thefirstsql.com
Updating a % of cells in a Column
Posted - 02/21/2013 : 10:14:09
