Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have two variables where users can input a start date and end date.
Let's called it:
vStartDate
vEndDate
Then I may have a table like this:
Payments:
LOAD
PaymentID,
<Create payment date based on vStartDatr and vEndDate here> PaymentDate,
CustomerID,
ProductID
AutoGenerate 300;
How can I get the PaymentDate to have a date randomly generated from the vStartDate and vEndDate?
I was somehow hoping that John would jump in
Maybe we should subtract a small number like Sebastien suggested to be on the safe side, or maybe we could use something like
Date(
RangeMin(
RangeMax(
$(vStartDate)+ Round(Rand() * ($(vEndDate) - $(vStartDate)+1)-0.5)
,$(vStartDate)
)
,$(vEndDate)
)
) as PaymentDate
This should be super-safe, shouldn't it?
I'd personally let the possible 1 slide. I suspect the function returns [0,1). And if I'm wrong, I believe there's a 1/(2^52) chance of getting 1. I can live with that. I like to floor() my rand() since I believe in [0,1), so my variation on the theme would be:
date($(vStartDate)+floor(rand()*($(vEndDate)-$(vStartDate)+1))) as PaymentDate
Say we're May 1 to May 10 of 2016.
0.0 - <0.1 : May 1
0.1 - <0.2 : May 2
...
0.9 - <1.0 : May 10
1 exactly : May 11 (may not be possible, is at least highly improbable)
Looks like an equal chance of each date.
EDIT: 1/(2^51+1) chance based on a guess of how they'd tackle including 1 by eliminating one of the random bits
Would using a Ceil without adding the one a not so good option here?
Date($(vStartDate) + Ceil(Rand() * ($(vEndDate) - $(vStartDate)))) as PaymentDate
UPDATE: Or I should say equal to Floor?
I believe that makes it virtually impossible to get vStartDate. You're randomly distributed between the other dates, so that much is good.
UPDATE: Using floor() isn't why I add 1. I add 1 because I need to randomly distribute between the right number of dates. May 10 - May 1 = 9 days, but I need 10 days.
Alright, I need to go back and test what you just mentioned. To me both looked the same , but I might be mistaken and won't realize the difference until I run both the options.
Thanks
Let us know. I HATE it when people tell me "that won't work because" without testing it, and I totally just did that to you. Even if I turn out to be right, sorry!
I don't HATE it John, gives me an option to try, test and learn a new thing. If would have told me that, Yes its the same, I would have trusted you and would have never went back and tested it at my end. But this give me an opportunity to go back and test and learn it
I almost did it to someone else a few weeks back. It might have been Stephan. Someone wrote an expression that I "knew" was wrong, and I almost said so without testing it, but then I got smart and tested it first. It worked, and once I saw it work, I understood why it worked. Almost put my foot in my mouth. Learned something instead.
Moral of the story: Testing good! Jumping to conclusions bad!