Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating a % of cells in a Column Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 8:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 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
Posted Thursday, February 21, 2013 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Posted Thursday, February 21, 2013 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, Visits: 155
Thanks Chris, Post edited.
Post #1422638
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse