Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Update query will not run? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, February 20, 2013 5:30 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 I'll help a little further. The script you posted (althought syntactically incorrect) does exactly what the RN_MULTINOMIAL function does:`UPDATE BOOKINGSSET DepartureDateCASE WHEN RAND() Result = Between 0 and 0.3 = Departure Date will be 2 Nights Later CASE WHEN RAND() Result = Between 0.3 and 0.4 = Departure Date will be 3 Nights LaterCASE WHEN RAND ()Result >0.4 = Departure Date will be either 1,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28 Nights Later`To put this in a context that might be more familiar to you, what you'll want to do is something like this (after setting up the @MultinomialProbabilities Distribution table:`UPDATE bSET DepartureDate = DATEADD(day, dbo.RN_MULTINOMIAL(@MultinomialProbabilities, URN), Arrivaldate)FROM BOOKINGS bCROSS APPLY (SELECT URN=RAND(CHECKSUM(NEWID()))) a`Your boss should be happy because you used a CASE statement too (in the setup of the @MultinomialProbabilities Distribution table). My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1422365
 Posted Thursday, February 21, 2013 3:05 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, May 7, 2013 3:30 AM Points: 62, Visits: 155
 I was given a simple way of doing this:UPDATE BOOKINGS SET DepartureDate = DATEADD(day, CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.3 THEN 2 ELSE CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.3 and 0.5 THEN 3 ELSE Round(Rand(CHECKSUM(NEWID())) * 28,0) END END,ArrivalDate) Thanks for all your help.
Post #1422467
 Posted Thursday, February 21, 2013 3:38 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 wafw1971 (2/21/2013)I was given a simple way of doing this:UPDATE BOOKINGS SET DepartureDate = DATEADD(day, CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.3 THEN 2 ELSE CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.3 and 0.5 THEN 3 ELSE Round(Rand(CHECKSUM(NEWID())) * 28,0) END END,ArrivalDate) Thanks for all your help.I suggest you double check your math but good for you. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1422481
 Posted Thursday, February 21, 2013 3:54 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, May 7, 2013 3:30 AM Points: 62, Visits: 155
 Hi Dwain, I didn't mean to offend you, if that's the case I am sorry. My boss helped me with that query, is it not right?1.35 Million Records of which30% should 2 night stays 20% should be 3 night staysand the other 50% to be randomised between 1, 4 and 28 days.I do have another question if you could help in the same vain.I now need to make 15% of them cancelled by inserting a random Cancelled Date. However the cancelled date must be =>Booking Date and <=Arrival Date.I have completed the random section but I now need to know how to add greater than and less than part to the query:Can you help?SELECT ArrivalDate, DATEADD(day, CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.85 THEN NULL ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.85 and 0.88 THEN 0 ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.88 and 0.92 THEN -1 ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.97 THEN -7 ELSERound(Rand(CHECKSUM(NEWID())) * -90,0) END END END END,ArrivalDate) AS DaystoReduce FROM BookingsThanksWayne
Post #1422487
 Posted Thursday, February 21, 2013 4:59 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 wafw1971 (2/21/2013)Hi Dwain, I didn't mean to offend you, if that's the case I am sorry. My boss helped me with that query, is it not right?1.35 Million Records of which30% should 2 night stays 20% should be 3 night staysand the other 50% to be randomised between 1, 4 and 28 days.I do have another question if you could help in the same vain.I now need to make 15% of them cancelled by inserting a random Cancelled Date. However the cancelled date must be =>Booking Date and <=Arrival Date.I have completed the random section but I now need to know how to add greater than and less than part to the query:Can you help?SELECT ArrivalDate, DATEADD(day, CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0 and 0.85 THEN NULL ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.85 and 0.88 THEN 0 ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.88 and 0.92 THEN -1 ELSECASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.97 THEN -7 ELSERound(Rand(CHECKSUM(NEWID())) * -90,0) END END END END,ArrivalDate) AS DaystoReduce FROM BookingsThanksWayneNo offense taken. I was referring to this line:`Round(Rand(CHECKSUM(NEWID())) * 28,0) END END,ArrivalDate) `Which I believe will throw some 0s, 2s and 3s into the mix.I know a way to generate 15% random cancellations as you say you need, however to do it I need you to provide me with some DDL for the table and some test data in consumable form. It is not something I can just write up without testing and expect to get it right.The way I do it might be easier or harder depending on what key fields are available to work with (e.g., if a unique booking number is present). My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1422503
 Posted Thursday, February 21, 2013 5:15 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, May 7, 2013 3:30 AM Points: 62, Visits: 155
 This is what my table looks like:Booking_Skey BookingNumber ArrivalDate DepartureDate BookingDate CancelledDate BookingValue PitchType_Skey Site_Skey1 B00000001 2010-01-02 2010-01-23 2009-12-26 NULL NULL 4 22 B00000002 2010-01-02 2010-01-05 2009-12-26 NULL NULL 4 23 B00000003 2010-01-02 2010-01-05 2009-12-04 NULL NULL 4 24 B00000004 2010-01-02 2010-01-16 2010-01-02 NULL NULL 4 25 B00000005 2010-01-02 2010-01-04 2009-12-26 NULL NULL 4 26 B00000006 2010-01-02 2010-01-29 2009-12-22 NULL NULL 4 27 B00000007 2010-01-03 2010-01-31 2009-10-05 NULL NULL 4 28 B00000008 2010-01-03 2010-01-24 2010-01-01 NULL NULL 4 29 B00000009 2010-01-04 2010-01-24 2010-01-03 NULL NULL 4 210 B00000010 2010-01-04 2010-01-06 2009-12-28 NULL NULL 4 2I hope that helps.Thanks
Post #1422506
 Posted Thursday, February 21, 2013 5:48 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 2:23 AM Points: 2,194, Visits: 7,781
 Sorry to jump in late into the party and all, but if you want 30% should 2 night stays 20% should be 3 night staysand 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 BookingsORDER BY NEWID())UPDATE TwoDaysSET 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 wantSELECT TOP 28 PERCENT Booking,ArrivalDate,DepartureDate FROM BookingsWHERE DepartureDate IS NULLORDER BY NEWID())UPDATE ThreeDaysSET 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 daysSELECT Booking,ArrivalDate,DepartureDate,1 + (ROW_NUMBER() OVER(ORDER BY NEWID()) % 3) AS SegmentFROM BookingsWHERE DepartureDate IS NULL)UPDATE RemainderSET 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,CancelledFROM BookingsORDER BY NEWID())UPDATE CancellationsSET Cancelled={whatever you want to put in here}` MM`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw
Post #1422514
 Posted Thursday, February 21, 2013 6:00 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 Magoo you've done it again! (love that catchphrase)Seriously, +1 because that's I think the OP needs. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1422519
 Posted Thursday, February 21, 2013 6:11 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 2:23 AM Points: 2,194, Visits: 7,781
 dwain.c (2/21/2013)Magoo you've done it again! (love that catchphrase)Seriously, +1 because that's I think the OP needs.Can't hear it enough TBH MM`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw
Post #1422525
 Posted Thursday, February 21, 2013 6:13 AM
 Valued Member Group: General Forum Members Last Login: Tuesday, May 7, 2013 3:30 AM Points: 62, Visits: 155
 Thanks everybody I really do appreciate the help, one question though. When you provide the script/query/code (see I have no idea) is there any other way to copy it without doing it a little at a time?
Post #1422528

 Permissions