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?
May be like this:
Date($(vStartDate) + Ceil(Rand() * ($(vEndDate) - $(vStartDate)))) as PaymentDate
To get an equal chance to create dates in your range, try something like
Date($(vStartDate)+ Round(Rand() * ($(vEndDate) - $(vStartDate)+1)-0.5)) as PaymentDate
and take care that your variables expand to a numeric representation.
Hi Swuehl,
It's a good thing to get the same chance for extremity date (vStartDate, vEndDate) than for others dates to be selected. But you will have a problem if Rand() return 1, you will have vEndDate+1 returned...
You need to modify the expression like that:
Date($(vStartDate)+ Round(Rand() * ($(vEndDate) - $(vStartDate)+0.999)-0.5)) as PaymentDate
Actually, I don't really know if the RAND() function returns values in the range [0,1] or ]0,1[ or a half - open variant.
HIC once mentioned [0,1] in one of his blog post but the HELP just states
The function returns a random number between 0 and 1
Anyone ever seen an integer 1 returned ?
On a AutoGenerate of 100M, the max Rand() * 10000000000 I got was 9999999986.0302
Table:
LOAD Rand() * 10000000000 as Num
AutoGenerate 100000000;
So I guess Stefan might be right.
I tend to trust HIC, but [0,1] strikes me as very strange. I would expect [0,1) as we get with Excel and Java.
Here's a possible argument that it's likely [0,1). We have 52 bits to play with in IEEE 754 double-precision binary floating point, and it would be very rational to interpret that as a number between 0.00000... and 0.11111... binary, which would be [0,1). You could go [0,1], but you'd have to lose one of those random bits to do so, so you'd have only half as many random numbers.
You'd have to run FAR more than 100M to have any sort of statistical certainty that 1 is being excluded, and are likely to have to run FAR more than 100M to see if 0 is included. I suppose that's an argument for "it doesn't matter". Your chance of ever seeing exactly 0 or 1, even if they're included, is extremely remote. Well, assuming actual randomness, which isn't the case. Hard to know with pseudo-random numbers. But I hate "this is statistically unlikely to cause you a problem" as an answer.
I wish had the computing power to go to a level where the test might give more conclusive results. But just based on a 'SINGLE SMALL' sample test I deduced that Stefan is right.
Am I being a good statistician? No.
Am I being a good friend? Hell Ya!