Generating Test Data: Part 2 - Generating Sequential and Random Dates

Question

Generating Test Data: Part 2 - Generating Sequential and Random Dates

Jeff Moden

SSC Guru

Points: 1004402
More actions
April 24, 2012 at 9:40 pm

#255422

Comments posted to this topic are about the item Generating Test Data: Part 2 - Generating Sequential and Random Dates
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Thanks Jeff, another great article with code that everyone should add to their sandbox DB. And of course, use same, to test if an item is ready for production.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

You da man Jeff! Another great article!

I've seen quite a few forum posts recently where your discussion of how DATETIMEs actually work would have assisted the posters had they understood this fundamental concept.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 3

Superbly balanced article. But in true Oliver Twist fashion, "Can I have some more?"

In the real-world datetime data I've dealt with, they often follow roughly cyclical patterns, not pure random patterns. For example, if you look at say ambient temperature in the town in which you live, over time it might approximate a sort-of sine wave from day to day, but also the larger sine wave of seasonal change. Similarly, if you look at something like Emergency Room attendances, there is a daily, weekly, and seasonal repeating pattern of attendance numbers. If you want to generate test ER attendance datetimes, you don't want them to be strictly random, because in the long run, you'll generate about the same number of ER attendances (for example) for 3:00AM as you will for 3:00PM. You instead, want it to be random within certain parameters. If you knew the mean and standard deviation of the number of attendances of the 24 hours of the day, by the seven days of the week, by the 4 (in my case) seasons of the year (672 rows of reference data), I'm wondering whether you could combine that knowledge with Jeff's techniques to generate test data that closely approximates the patterns seen in reality.

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

Dwain Camps SSC Guru Points: 86978 More actions · Answer 4

GPO - Not always as easy as we would like.

I have a vague recoolection that you can convert uniform random numbers to another distribution by multiplying by the inverse of the probability density function.

Poisson distributions are challenging because the algorithms I've seen for generating Poisson random numbers (hospital arrivals for example) use an iterative approach.

Sinusoidal functions might be a little easier but may be calculated differently. For example, you may expect temperatures to fluctuate randomly around a + or - band on the sinusoidal wave.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Hey Jeff, can you put a downloadable file with the relevant operational code parts of the post? Thanks in advance, and wonderful stuff as always!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Scott Levy SSC Enthusiast Points: 150 More actions · Answer 6

Just to leap to Excel's defence there is a reason that it thinks 1900 is a leap year.

Joel Spolsky can across it while working on Excel in 1991.

Dwain Camps SSC Guru Points: 86978 More actions · Answer 7

Jeff - What is it about you that attracts spam so well? Your cologne perhaps?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 8

The spam people clearly target the articles that everyone is going to read...

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

bitbucket-25253 (4/24/2012)
Thanks Jeff, another great article with code that everyone should add to their sandbox DB. And of course, use same, to test if an item is ready for production.

As always, very good hearing from you, ol' friend. Thanks for stopping by, Ron.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

dwain.c (4/25/2012)
You da man Jeff! Another great article!
I've seen quite a few forum posts recently where your discussion of how DATETIMEs actually work would have assisted the posters had they understood this fundamental concept.

There's quite a bit more as to how they actually work behind the scenes but few people ever need to go there... including me. 😉

Thanks for the feedback, Dwain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

GPO (4/25/2012)
Superbly balanced article. But in true Oliver Twist fashion, "Can I have some more?"
In the real-world datetime data I've dealt with, they often follow roughly cyclical patterns, not pure random patterns. For example, if you look at say ambient temperature in the town in which you live, over time it might approximate a sort-of sine wave from day to day, but also the larger sine wave of seasonal change. Similarly, if you look at something like Emergency Room attendances, there is a daily, weekly, and seasonal repeating pattern of attendance numbers. If you want to generate test ER attendance datetimes, you don't want them to be strictly random, because in the long run, you'll generate about the same number of ER attendances (for example) for 3:00AM as you will for 3:00PM. You instead, want it to be random within certain parameters. If you knew the mean and standard deviation of the number of attendances of the 24 hours of the day, by the seven days of the week, by the 4 (in my case) seasons of the year (672 rows of reference data), I'm wondering whether you could combine that knowledge with Jeff's techniques to generate test data that closely approximates the patterns seen in reality.

"More please?" is the normal question after any such introductory article and I thank you for your thoughtful feedback.

I've done such things in more of a stepped fashion but not in a nice, smooth sinusoidal fashion. I suppose that it just a matter of making smaller steps. I'll have to think about that. Thank you, again, for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cadavre SSC-Forever Points: 41690 More actions · Answer 12

Nicely explained Jeff.

I may have to "borrow" your method of generating random DATETIME data, my method is more difficult to understand when people glance at it. 😀

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 13

TheSQLGuru (4/25/2012)
Hey Jeff, can you put a downloadable file with the relevant operational code parts of the post? Thanks in advance, and wonderful stuff as always!

Heh... stealing an idea from Lotus isn't my idea of a laudable defense. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 14

Cadavre (4/26/2012)
Nicely explained Jeff.
I may have to "borrow" your method of generating random DATETIME data, my method is more difficult to understand when people glance at it. 😀

Borrow away. These aren't my methods. They pretty well standard for folks that have been using NEWID() for such things over the years because they fall into the classic random number mathematical formulas.

If you don't mind, could you post your method? It's always interesting to see how others do things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)