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


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


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

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88284 Visits: 41128
Dev (5/20/2012)
Nice Article Jeff!!!


Thanks, Dev. I appreciate both the read and 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
WayneS
WayneS
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: 10058 Visits: 10574
Great article Jeff.

One thing to mention... in the article, you pointed out that you cannot directly add / subtract a number to the new DATE data time. This applies to all of the new date data types: DATETIME2, DATETIMEOFFSET and TIME as well as DATE.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

boriskey
boriskey
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 256
Jeff, thanks for the great articles (part 1 and part 2 and look forward to part3).

Is there a way to pick a random value (like a color) from a subquery? here is what I tried but getting the same value on every run:

SELECT TOP (100)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as ID, --Sequential number from 1 to ..
(SELECT top 1 color FROM ( VALUES (0,'Red'),(1,'Green'),(2,'Yellow') ) colors(id,color) ORDER BY NEWID()) as RandomColor,
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

but if I run this piece alone, I am getting different colors:
SELECT top 1 color FROM ( VALUES (0,'Red'),(1,'Green'),(2,'Yellow') ) colors(id,color) ORDER BY NEWID()
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88284 Visits: 41128
No... not from a sub-query. At least I haven't figured out a way to do it with a TOP 1/ORDER BY like that.

You could do it using a CASE function based on ABS(CHECKSUM(NEWID()))%3 but that will actually come up with 4 values because, since NEWID() isn't deterministic, the CASE function will recalculate each and every WHEN even if you use CASE ABS(CHECKSUM(NEWID()))%3. You could then change the formula to ABS(CHECKSUM(NEWID()))%2 and use ELSE but that will give you an uneven distribution.

So, the only thing to do is to gen the numbers that control the color separately and then CASE that number. Here's one way of doing that in a single query.

DROP TABLE #MyHead;
WITH
cteRandom AS
( --=== Generate the random number first
SELECT TOP (1000)
ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
RandomColor# = ABS(CHECKSUM(NEWID()))%3
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
) --=== Now, pick the color according to the generated number
SELECT ID,
RandomColor = CASE RandomColor#
WHEN 1 THEN 'Red'
WHEN 2 THEN 'Green'
ELSE 'Yellow'
END
INTO #MyHead
FROM cteRandom
;
--===== Show the distribution
SELECT RandomColor, COUNT(*)
FROM #MyHead
GROUP BY RandomColor
ORDER BY RandomColor



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88284 Visits: 41128
Here's another way... same idea, though.

DROP TABLE #MyHead;

SELECT TOP (1000)
ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
RandomColor = CASE RandomColor#
WHEN 1 THEN 'Red'
WHEN 2 THEN 'Green'
ELSE 'Yellow'
END
INTO #MyHead
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS APPLY (SELECT ABS(CHECKSUM(NEWID()))%3) ca (RandomColor#)
;
--===== Show the distribution
SELECT RandomColor, COUNT(*)
FROM #MyHead
GROUP BY RandomColor
ORDER BY RandomColor
;



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
boriskey
boriskey
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 256
Jeff Moden (12/16/2012)
Here's another way... same idea, though.


awesome, it will work! thanks for the quick response, Jeff! and I am going to use this opportunity to say BIG THANK YOU for all your articles and knowledge you shared with us!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88284 Visits: 41128
Thanks, Boriskey Blush I really appreciate the feedback. I aim to please... I sometimes miss but I'm always aiming. :-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Rempel
John Rempel
SSC Eights!
SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)

Group: General Forum Members
Points: 997 Visits: 288
Jeff, moving the (SELECT ABS(CHECKSUM(NEWID()))%3) from the CROSS APPLY into the CASE seems to work as well. When should CROSS APPLY be used, or is it equivalent in this case?


SELECT TOP (1000)
ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
RandomColor = CASE (SELECT ABS(CHECKSUM(NEWID()))%3)
WHEN 1 THEN 'Red'
WHEN 2 THEN 'Green'
ELSE 'Yellow'
END
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;



Thanks much. I always enjoy reading your articles and comments. And I always learn or re-learn something.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88284 Visits: 41128
Sorry... I didn't see the new code correctly. I'll be back.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88284 Visits: 41128
John Rempel (8/28/2013)
Jeff, moving the (SELECT ABS(CHECKSUM(NEWID()))%3) from the CROSS APPLY into the CASE seems to work as well. When should CROSS APPLY be used, or is it equivalent in this case?


SELECT TOP (1000)
ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
RandomColor = CASE (SELECT ABS(CHECKSUM(NEWID()))%3)
WHEN 1 THEN 'Red'
WHEN 2 THEN 'Green'
ELSE 'Yellow'
END
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;





If you look deep in the properties of some of the compute scalars in the actual execution plan, you see that this has the same problem I spoke of before. Here's where...

Scalar Operator(CASE WHEN [Expr1006]=(1) THEN 'Red' ELSE CASE WHEN [Expr1007]=(2) THEN 'Green' ELSE 'Yellow' END END)

Expr1006 and Expr1007 are separate copies of the Scalar Operator(abs(checksum(newid()))%(3)). What ends up happening is that Yellow will turn up more than the other two colors because if the first random forumula isn't a 1, then it calculates a new random formula. Each random number generator only has a 1 out of 3 chance of finding its mark. That means that 2/3rds of the time, each will miss its mark. I don't know what that works out to odds wise (no coffee in the last 5 hours), but it means that Yellow will always come out with the most hits followed by Red, followed by Green. The CROSS APPLY doesn't have the same problem.

Thanks much. I always enjoy reading your articles and comments. And I always learn or re-learn something.



Thank you very much for the feedback. I'm glad that I can return something to the community that has helped me so much.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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