Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Generating Test Data: Part 2 - Generating Sequential and Random Dates Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, April 24, 2012 9:40 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:44 AM Points: 42,063, Visits: 39,445
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1289524
 Posted Tuesday, April 24, 2012 9:44 PM
 SSCertifiable Group: General Forum Members Last Login: Sunday, January 4, 2015 7:55 AM Points: 5,333, Visits: 25,280
Post #1289525
 Posted Wednesday, April 25, 2012 12:04 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!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
Post #1289552
 Posted Wednesday, April 25, 2012 6:01 AM
 Ten Centuries Group: General Forum Members Last Login: Saturday, November 12, 2016 6:57 PM Points: 1,006, Visits: 1,898
 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 one's work is terribly important.Bertrand Russell
Post #1289769
 Posted Wednesday, April 25, 2012 7:08 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 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!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
Post #1289828
 Posted Wednesday, April 25, 2012 8:56 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 7:34 AM Points: 5,656, Visits: 8,179
 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. BolesSQL Server ConsultantSQL MVP 2007-2012TheSQLGuru at GMail
Post #1289959
 Posted Wednesday, April 25, 2012 4:56 PM
 Grasshopper Group: General Forum Members Last Login: Sunday, January 11, 2015 7:22 PM Points: 12, Visits: 79
 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.
Post #1290331
 Posted Wednesday, April 25, 2012 7:44 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 Jeff - What is it about you that attracts spam so well? Your cologne perhaps? 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
Post #1290377
 Posted Wednesday, April 25, 2012 7:47 PM
 Ten Centuries Group: General Forum Members Last Login: Saturday, November 12, 2016 6:57 PM Points: 1,006, Visits: 1,898
 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 one's work is terribly important.Bertrand Russell
Post #1290379
 Posted Thursday, April 26, 2012 6:49 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:44 AM Points: 42,063, Visits: 39,445
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1290640

 Permissions