Post reply

Updating a % of cells in a Column

  • February 21, 2013 at 8:12 am

    #289813

    I have 4 million rows in my table with a blank column called Cancelled Bookings divided into 3 years 2010, 2011 and 2012

    Booking_SkeyBookingNumberArrivalDateDepartureDateBookingDateCancelledDateBookingValuePitchType_SkeySite_Skey

    124532B001245322010-12-312011-01-022010-12-31NULL10.0072

    What 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 days

    USE Occupancy

    SELECT ArrivalDate,

    DATEADD(day,

    CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.92 THEN NULL ELSE

    CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.94 THEN 0 ELSE

    CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.94 and 0.96 THEN -1 ELSE

    CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.96 and 0.98 THEN -7 ELSE

    Round(Rand(CHECKSUM(NEWID())) * -90,0) END END END END, ArrivalDate) AS DaystoReduce

    FROM Bookings

    WHERE DATEPART(Year,ArrivalDate) = '2010' and CancelledDate BETWEEN ArrivalDate AND DepartureDate

    Can you help?

    Thanks

    Wayne

  • February 21, 2013 at 8:34 am

    #1589998

    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 Shaw

    For 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 Moden

  • February 21, 2013 at 8:42 am

    #1590006

    Thanks Chris, Post edited.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply