Updating a % of cells in a Column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Updating a % of cells in a Column

Updating a % of cells in a Column

Rate Topic

Display Mode

Topic Options

Author

Message

wafw1971

Posted Thursday, February 21, 2013 8:12 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:30 AM
Points: 62, Visits: 155

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

Booking_Skey BookingNumber ArrivalDate DepartureDate BookingDate CancelledDate BookingValue PitchType_Skey Site_Skey
124532 B00124532 2010-12-31 2011-01-02 2010-12-31 NULL 10.00 7 2
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

Post #1422616

ChrisM@Work

Posted Thursday, February 21, 2013 8:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:55 AM
Points: 5,612, Visits: 10,977

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1422630

wafw1971

Posted Thursday, February 21, 2013 8:42 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:30 AM
Points: 62, Visits: 155

Thanks Chris, Post edited.

Post #1422638

« Prev Topic | Next Topic »

Permissions