SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update query will not run?


Update query will not run?

Author
Message
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18141 Visits: 6431
I'll help a little further. The script you posted (althought syntactically incorrect) does exactly what the RN_MULTINOMIAL function does:


UPDATE BOOKINGS
SET DepartureDate
CASE 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 Later
CASE 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 b
SET DepartureDate = DATEADD(day,
dbo.RN_MULTINOMIAL(@MultinomialProbabilities, URN), Arrivaldate)
FROM BOOKINGS b
CROSS 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). :-P


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
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 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.
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18141 Visits: 6431
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
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 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 which
30% should 2 night stays
20% should be 3 night stays
and 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 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.85 and 0.88 THEN 0 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.88 and 0.92 THEN -1 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.97 THEN -7 ELSE
Round(Rand(CHECKSUM(NEWID())) * -90,0) END END END END,ArrivalDate) AS DaystoReduce
FROM Bookings

Thanks

Wayne
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18141 Visits: 6431
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 which
30% should 2 night stays
20% should be 3 night stays
and 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 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.85 and 0.88 THEN 0 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.88 and 0.92 THEN -1 ELSE
CASE WHEN Rand(CHECKSUM(NEWID())) BETWEEN 0.92 and 0.97 THEN -7 ELSE
Round(Rand(CHECKSUM(NEWID())) * -90,0) END END END END,ArrivalDate) AS DaystoReduce
FROM Bookings

Thanks

Wayne


No 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
wafw1971
wafw1971
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 155
This is what my table looks like:

Booking_Skey BookingNumber ArrivalDate DepartureDate BookingDate CancelledDate BookingValue PitchType_Skey Site_Skey
1 B00000001 2010-01-02 2010-01-23 2009-12-26 NULL NULL 4 2
2 B00000002 2010-01-02 2010-01-05 2009-12-26 NULL NULL 4 2
3 B00000003 2010-01-02 2010-01-05 2009-12-04 NULL NULL 4 2
4 B00000004 2010-01-02 2010-01-16 2010-01-02 NULL NULL 4 2
5 B00000005 2010-01-02 2010-01-04 2009-12-26 NULL NULL 4 2
6 B00000006 2010-01-02 2010-01-29 2009-12-22 NULL NULL 4 2
7 B00000007 2010-01-03 2010-01-31 2009-10-05 NULL NULL 4 2
8 B00000008 2010-01-03 2010-01-24 2010-01-01 NULL NULL 4 2
9 B00000009 2010-01-04 2010-01-24 2010-01-03 NULL NULL 4 2
10 B00000010 2010-01-04 2010-01-06 2009-12-28 NULL NULL 4 2

I hope that helps.

Thanks
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10771 Visits: 7891
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
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • dwain.c
    dwain.c
    SSCoach
    SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

    Group: General Forum Members
    Points: 18141 Visits: 6431
    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
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10771 Visits: 7891
    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 :-P

    MM


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




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

  • wafw1971
    wafw1971
    SSC-Enthusiastic
    SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

    Group: General Forum Members
    Points: 164 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?
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search