InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 08:26:13 GMT20http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxThanks MM saves a lot of time knowing that.Thu, 21 Feb 2013 06:30:25 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspx[quote][b]wafw1971 (2/21/2013)[/b][hr]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?[/quote]Mouse down just before the code window, then drag the mouse below the code window - this will highlight everything in the window, then copyThu, 21 Feb 2013 06:18:30 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxThanks 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?Thu, 21 Feb 2013 06:13:56 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspx[quote][b]dwain.c (2/21/2013)[/b][hr]Magoo you've done it again! (love that catchphrase)Seriously, +1 because that's I think the OP needs.[/quote]Can't hear it enough TBH :-PThu, 21 Feb 2013 06:11:59 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxMagoo you've done it again! (love that catchphrase)Seriously, +1 because that's I think the OP needs.Thu, 21 Feb 2013 06:00:14 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxSorry 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. [code="sql"];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}[/code]Thu, 21 Feb 2013 05:48:51 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxThis 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.ThanksThu, 21 Feb 2013 05:15:27 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspx[quote][b]wafw1971 (2/21/2013)[/b][hr]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[/quote]No offense taken. I was referring to this line:[code="sql"]Round(Rand(CHECKSUM(NEWID())) * 28,0) END END,ArrivalDate) [/code]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).Thu, 21 Feb 2013 04:59:45 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxHi 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 BookingsThanksWayneThu, 21 Feb 2013 03:54:46 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspx[quote][b]wafw1971 (2/21/2013)[/b][hr]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.[/quote]I suggest you double check your math but good for you.Thu, 21 Feb 2013 03:38:29 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxI 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.Thu, 21 Feb 2013 03:05:52 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxI'll help a little further. The script you posted (althought syntactically incorrect) does exactly what the RN_MULTINOMIAL function does:[code="sql"]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[/code]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:[code="sql"]UPDATE bSET DepartureDate = DATEADD(day, dbo.RN_MULTINOMIAL(@MultinomialProbabilities, URN), Arrivaldate)FROM BOOKINGS bCROSS APPLY (SELECT URN=RAND(CHECKSUM(NEWID()))) a[/code]Your boss should be happy because you used a CASE statement too (in the setup of the @MultinomialProbabilities Distribution table). :-PWed, 20 Feb 2013 17:30:08 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxThank you for that I will try and get my head around it tomorrow.Wed, 20 Feb 2013 09:23:06 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxPerhaps one additional clarification.To generate a single multinomial random number, you do it like this:[code="sql"]SELECT MNRN=dbo.RN_MULTINOMIAL(@MultinomialProbabilities, URN)FROM (SELECT URN=RAND(CHECKSUM(NEWID()))) a[/code]Provided because I wasn't sure if when you referring to your skill level you meant in SQL or statistics.Wed, 20 Feb 2013 08:48:52 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxHere is how to generate a sample set of multinomially distributed random numbers. First, you need to create a TYPE and a FUNCTION by running this script:[code="sql"]CREATE TYPE Distribution AS TABLE (EventID INT, EventProb FLOAT, CumProb FLOAT) GOCREATE FUNCTION dbo.RN_MULTINOMIAL (@Multinomial Distribution READONLY, @URN FLOAT)RETURNS INT --Cannot use WITH SCHEMABINDING ASBEGIN RETURN ISNULL( ( SELECT TOP 1 EventID FROM @Multinomial WHERE @URN < CumProb ORDER BY CumProb) -- Handle unlikely case where URN = exactly 1.0 ,( SELECT MAX(EventID) FROM @Multinomial))END[/code]Next, you need to set up your multinomial probability distribution table as follows:[code="sql"]DECLARE @MultinomialProbabilities Distribution;WITH Tally (n) AS ( SELECT TOP 28 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)INSERT INTO @MultinomialProbabilitiesSELECT n ,CASE n WHEN 1 THEN .6/26. WHEN 2 THEN .3 WHEN 3 THEN .1 ELSE .6/26. END ,CASE n WHEN 1 THEN .6*1./26. WHEN 2 THEN .3+.6*1./26. WHEN 3 THEN .4+.6*1./26. ELSE .4+.6*(n-2)/26. ENDFROM TallySELECT * FROM @MultinomialProbabilities[/code]Note how the EventProb column shows .3 for event 2 and .1 for event 3. The rest are all the remaining probability (.6) divided by the number of events (26). The last column is the cumulative probability for all previous events (last row should show 1).The hard part is now behind us.Now, within the same SQL batch as the above, this test harness tests the generated random numbers so you can compare to the distribution's expected frequency.[code="sql"]DECLARE @TestNums INT = 1000;WITH Tally (n) AS ( SELECT TOP (@TestNums) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)SELECT MNRN, CountOfMNRNs=COUNT(MNRN), ActualProbability=COUNT(MNRN)/(1.*@TestNums)FROM ( [b]SELECT MNRN=dbo.RN_MULTINOMIAL(@MultinomialProbabilities, URN) FROM Tally CROSS APPLY (SELECT URN=RAND(CHECKSUM(NEWID()))) a[/b] ) aINNER JOIN @MultinomialProbabilities ON EventID=MNRNGROUP BY MNRN[/code]The key to generating a group of random numbers is the part I highlighted in [b]bold[/b]/ This generates a sample set based on the value of @TestNums. The rest of it just groups by EventID and calculates the actual probability. This should center around 0.23 for all events except 2 and 3, which should be close to .3 and .1. The more numbers you generate, the closer they should be to the actual distribution.Hope this helps.Wed, 20 Feb 2013 08:39:07 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxHi DwainI have tried to breakdown what I need below, I hope this helps.ThanksWayneStep 1Arrival Date (Already generated) – 1.35 Million TimesStep 2Randomise a number between 0 and 1 Step 3Use the Randomised number produced above to create the script belowUPDATE 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 LaterWed, 20 Feb 2013 08:27:16 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxOK. Give me a few minutes and I'll code up an example.Wed, 20 Feb 2013 08:11:33 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxHi DwainThanks for the link but to be honest its way above where I am in my training (I'm 4 weeks in) and to be honest that looks like a different language.My boss said I should be able to find a case statement along the lines if a Random number genrte between 0 and 1 then between 0 and 0.3 is a 2 day stay and between 0.3 to 0.4 is a 3 day stay else 0.4 to 1 will all other days between 1 and 28.But I don't know how to write the code.Can you help or point me in the right direction.TaWayneWed, 20 Feb 2013 07:20:38 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspx[quote][b]wafw1971 (2/20/2013)[/b][hr]I have just been told I have done it completely wrong by my boss and the query above hasn't randomised anything on our data, I have got 48000 records with a 2 night stay, 48000 records for a 27 night stay etc.What I need is 30% of the departure dates to be 2 days in length, 10% to be 3 Days and the rest to be randomised amongst 1,4 to 28.Can anyone help?[/quote]Possibly. What you need is to generate random numbers based on a multinomial distribution. See the second article in my signature links (about random number generators in SQL) for a function that will do this.Wed, 20 Feb 2013 07:09:24 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxI have just been told I have done it completely wrong by my boss and the query above hasn't randomised anything on our data, I have got 48000 records with a 2 night stay, 48000 records for a 27 night stay etc.What I need is 30% of the departure dates to be 2 days in length, 10% to be 3 Days and the rest to be randomised amongst 1,4 to 28.Can anyone help?Wed, 20 Feb 2013 07:01:59 GMTwafw1971http://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspx[quote][b]wafw1971 (2/20/2013)[/b][hr]The following query gives me a random date between 1 and 28 days after the arrival date:SELECT ArrivalDate, DATEADD(day, 1 + RAND(checksum(NEWID()))* LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate FROM Bookings, LengthOfStayHowever when I use the update query below it only gives me between 1 and 2 days after the arrival dateUSE OccupancyUpdate BSet DepartureDate = DATEADD(day, 1 + RAND(checksum(NEWID()))*1.5 * L.LengthofStay, B.ArrivalDate)FROM LengthOfStay L, Bookings BDoes anyone know why, and if so how do I change it?ThanksWayne[/quote]If you run this multiple times:[code="sql"]SELECT 1 + RAND(checksum(NEWID()))[/code]You should see that the number returned is always between 1 and 2.If you want it to return a number between 1 and 28, use this:[code="sql"]SELECT 1 + ABS(checksum(NEWID())) % 28[/code]Wed, 20 Feb 2013 03:38:11 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1421984-1292-1.aspxThe following query gives me a random date between 1 and 28 days after the arrival date:SELECT ArrivalDate, DATEADD(day, 1 + RAND(checksum(NEWID()))* LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate FROM Bookings, LengthOfStayHowever when I use the update query below it only gives me between 1 and 2 days after the arrival dateUSE OccupancyUpdate BSet DepartureDate = DATEADD(day, 1 + RAND(checksum(NEWID()))*1.5 * L.LengthofStay, B.ArrivalDate)FROM LengthOfStay L, Bookings BDoes anyone know why, and if so how do I change it?ThanksWayneWed, 20 Feb 2013 02:00:22 GMTwafw1971