Update query will not run?

• wafw1971

  SSC Eights!

  Points: 976

  More actions

  February 21, 2013 at 5:15 am

  #1589892

  This is what my table looks like:

  Booking_SkeyBookingNumberArrivalDateDepartureDateBookingDateCancelledDateBookingValuePitchType_SkeySite_Skey

  1B000000012010-01-022010-01-232009-12-26NULLNULL42

  2B000000022010-01-022010-01-052009-12-26NULLNULL42

  3B000000032010-01-022010-01-052009-12-04NULLNULL42

  4B000000042010-01-022010-01-162010-01-02NULLNULL42

  5B000000052010-01-022010-01-042009-12-26NULLNULL42

  6B000000062010-01-022010-01-292009-12-22NULLNULL42

  7B000000072010-01-032010-01-312009-10-05NULLNULL42

  8B000000082010-01-032010-01-242010-01-01NULLNULL42

  9B000000092010-01-042010-01-242010-01-03NULLNULL42

  10B000000102010-01-042010-01-062009-12-28NULLNULL42

  I hope that helps.

  Thanks

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  February 21, 2013 at 5:48 am

  #1589900

  Sorry to jump in late into the party and all, but if you want

  30% should 2 night stays

  20% should be 3 night stays

  and the other 50% to be randomised between 1, 4 and 28 days.

  Can I ask why you don't make life simple and just update 30% of the rows as 2 night stays, 20% as 3 night stays first...?

  e.g.

  ;WITH TwoDays AS

  (

  SELECT TOP 30 PERCENT Booking,ArrivalDate,DepartureDate

  FROM Bookings

  ORDER BY NEWID()

  )

  UPDATE TwoDays

  SET DepartureDate=DATEADD(day, 2,ArrivalDate) ;

  ;WITH ThreeDays AS

  (

  -- we have reduced the set by 30%, so 20% is now ~28% of the remaining 70%

  -- if you want to be more accurate about the percentages, just count the rows and precalculate how many you want

  SELECT TOP 28 PERCENT Booking,ArrivalDate,DepartureDate

  FROM Bookings

  WHERE DepartureDate IS NULL

  ORDER BY NEWID()

  )

  UPDATE ThreeDays

  SET DepartureDate=DATEADD(day, 3,ArrivalDate) ;

  ;WITH Remainder AS

  (

  -- we have reduced the set by 50%, so everything else is either 1 4 or 28 days

  -- depending on your definition of random, we could just say 1/3 of the remaining stays are 1 day, 1/3 are 4 days and 1/3 are 28 days

  SELECT Booking,ArrivalDate,DepartureDate,1 + (ROW_NUMBER() OVER(ORDER BY NEWID()) % 3) AS Segment

  FROM Bookings

  WHERE DepartureDate IS NULL

  )

  UPDATE Remainder

  SET DepartureDate=DATEADD(day,Case Segment WHEN 1 THEN 1 WHEN 2 THEN 4 WHEN 3 THEN 28 END,ArrivalDate) ;

  -- Cancel 15%

  ;WITH Cancellations AS

  (

  SELECT TOP 15 PERCENT Booking,Cancelled

  FROM Bookings

  ORDER BY NEWID()

  )

  UPDATE Cancellations

  SET Cancelled={whatever you want to put in here}

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]