InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 04:35:55 GMT20http://www.sqlservercentral.com/Forums/Topic1422616-1292-1.aspxThanks Chris, Post edited.Thu, 21 Feb 2013 08:42:10 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1422616-1292-1.aspx[quote][b]wafw1971 (2/21/2013)[/b][hr]I have 4 million lines of code.... [/quote]Four million lines of code would keep you quiet for a while! I guess you mean four million [i]rows[/i] in a [i]table[/i]?Read the section covering SELECT TOP... in Books Online, also NTILE(). Either of these could give you a good start.Thu, 21 Feb 2013 08:34:01 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1422616-1292-1.aspxI 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?ThanksWayneThu, 21 Feb 2013 08:12:32 GMTwafw1971