SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Updating a % of cells in a Column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/21/2013 :  10:14:09  Show Profile  Reply with Quote
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

Edited by - wafw1971 on 02/21/2013 11:18:57

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/22/2013 :  04:27:06  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Can you provide some sample data and the expected output for that data.

After a quick read of the problem, I don't understand the requirement.

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 02/22/2013 :  08:03:18  Show Profile  Reply with Quote
Ok, so what you're bascally doing is generating a table with sample-data, is that it? so it doesn't really matter which bookings are cancelled as long as you cancel according to the specifications? In that case my understanding is this: 2% of the total bookings (which is the equivaent of 20% of 8%) are cancelled on the same day as the Arrival Date, 2% are cancelled the day before the Arrival Date, etc... (the NewID()-stuff will totally kill performance):

Prerequisite: a UDF that generates a random integer between two parameters

--> Same day cancellations
;WITH cte AS (
  SELECT TOP 2 PERCENT Booking_Skey, ArrivalDate
  FROM Bookings
  WHERE DATEPART(Year,ArrivalDate) = '2010'
    AND CancelledDate IS NULL
  ORDER BY NewID()
  )
UPDATE cte SET CancelledDate = ArrivalDate

--> Day before cancellations
;WITH cte AS (
  SELECT TOP 2 PERCENT Booking_Skey, ArrivalDate
  FROM Bookings
  WHERE DATEPART(Year,ArrivalDate) = '2010'
    AND CancelledDate IS NULL
  ORDER BY NewID()
  )
UPDATE cte SET CancelledDate = ArrivalDate-1

--> Within a week before cancellations
;WITH cte AS (
  SELECT TOP 2 PERCENT Booking_Skey, ArrivalDate
  FROM Bookings
  WHERE DATEPART(Year,ArrivalDate) = '2010'
    AND CancelledDate IS NULL
  ORDER BY NewID()
  )
UPDATE cte SET CancelledDate = (ArrivalDate - dbo.Random(1, 7))

--> Within 3 months before cancellations
;WITH cte AS (
  SELECT TOP 2 PERCENT Booking_Skey, ArrivalDate
  FROM Bookings
  WHERE DATEPART(Year,ArrivalDate) = '2010'
    AND CancelledDate IS NULL
  ORDER BY NewID()
  )
UPDATE cte SET CancelledDate = (ArrivalDate - dbo.Random(1, 90))


- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000