## Updating a % of cells in a Column

 Author Message wafw1971 Old Hand Group: General Forum Members Points: 356 Visits: 155 I have 4 million rows in my table with a blank column called Cancelled Bookings divided into 3 years 2010, 2011 and 2012Booking_Skey BookingNumber ArrivalDate DepartureDate BookingDate CancelledDate BookingValue PitchType_Skey Site_Skey124532 B00124532 2010-12-31 2011-01-02 2010-12-31 NULL 10.00 7 2What I need to do is create a code where I can change the % of cancelations for the year I want to update:So for 2010 I need the following--Cancelled Bookings---- 8% of the total bookings are cancelled in the Year 2010, the cancellation date can be equal too of less than the Arrival Date and equal to or greater than the Booking Date-- 20% of the 8% are cancelled on the same day as the Arrival Date-- 20% of the 8% are cancelled the day before the Arrival Date-- 20% of the 8% are cancelled 7 days prior to the Arrival Date-- The rest of the cancellations are randomised between 1 and 90 daysUSE OccupancySELECT ArrivalDate, DATEADD(day, CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.92 THEN NULL ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.94 THEN 0 ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.94 and 0.96 THEN -1 ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.96 and 0.98 THEN -7 ELSERound(Rand(CHECKSUM(NEWID())) * -90,0) END END END END, ArrivalDate) AS DaystoReduce FROM BookingsWHERE DATEPART(Year,ArrivalDate) = '2010' and CancelledDate BETWEEN ArrivalDate AND DepartureDateCan you help?ThanksWayne ChrisM@Work SSC Guru Group: General Forum Members Points: 96365 Visits: 20667 wafw1971 (2/21/2013)I have 4 million lines of code.... Four million lines of code would keep you quiet for a while! I guess you mean four million rows in a table?Read the section covering SELECT TOP... in Books Online, also NTILE(). Either of these could give you a good start. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps wafw1971 Old Hand Group: General Forum Members Points: 356 Visits: 155 Thanks Chris, Post edited.