Sorry to jump in late into the party and all, but if you want
30% should 2 night stays
20% should be 3 night stays
and the other 50% to be randomised between 1, 4 and 28 days.
Can I ask why you don't make life simple and just update 30% of the rows as 2 night stays, 20% as 3 night stays first...?
e.g.
;WITH TwoDays AS
(
SELECT TOP 30 PERCENT Booking,ArrivalDate,DepartureDate
FROM Bookings
ORDER BY NEWID()
)
UPDATE TwoDays
SET DepartureDate=DATEADD(day, 2,ArrivalDate) ;
;WITH ThreeDays AS
(
-- we have reduced the set by 30%, so 20% is now ~28% of the remaining 70%
-- if you want to be more accurate about the percentages, just count the rows and precalculate how many you want
SELECT TOP 28 PERCENT Booking,ArrivalDate,DepartureDate
FROM Bookings
WHERE DepartureDate IS NULL
ORDER BY NEWID()
)
UPDATE ThreeDays
SET DepartureDate=DATEADD(day, 3,ArrivalDate) ;
;WITH Remainder AS
(
-- we have reduced the set by 50%, so everything else is either 1 4 or 28 days
-- depending on your definition of random, we could just say 1/3 of the remaining stays are 1 day, 1/3 are 4 days and 1/3 are 28 days
SELECT Booking,ArrivalDate,DepartureDate,1 + (ROW_NUMBER() OVER(ORDER BY NEWID()) % 3) AS Segment
FROM Bookings
WHERE DepartureDate IS NULL
)
UPDATE Remainder
SET DepartureDate=DATEADD(day,Case Segment WHEN 1 THEN 1 WHEN 2 THEN 4 WHEN 3 THEN 28 END,ArrivalDate) ;
-- Cancel 15%
;WITH Cancellations AS
(
SELECT TOP 15 PERCENT Booking,Cancelled
FROM Bookings
ORDER BY NEWID()
)
UPDATE Cancellations
SET Cancelled={whatever you want to put in here}
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);