Updating a % of cells in a Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Updating a % of cells in a Column

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-21 : 10:14:09

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-02-22 : 04:27:06

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/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-02-22 : 08:03:18

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources