Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Generating Test Data: Part 2 - Generating Sequential and Random Dates Expand / Collapse
Author
Message
Posted Sunday, May 20, 2012 3:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1303123
Posted Sunday, July 1, 2012 5:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:52 PM
Points: 6,581, Visits: 8,858
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
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
Post #1323595
Posted Sunday, December 16, 2012 7:15 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 1:38 PM
Points: 23, Visits: 219
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()
Post #1397057
Posted Sunday, December 16, 2012 9:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397070
Posted Sunday, December 16, 2012 9:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397073
Posted Monday, December 17, 2012 7:31 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 1:38 PM
Points: 23, Visits: 219
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!
Post #1397255
Posted Monday, December 17, 2012 6:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
Thanks, Boriskey I really appreciate the feedback. I aim to please... I sometimes miss but I'm always aiming.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1397490
Posted Wednesday, August 28, 2013 12:31 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 23, 2014 8:56 AM
Points: 945, Visits: 259
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.
Post #1489372
Posted Wednesday, August 28, 2013 9:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489505
Posted Wednesday, August 28, 2013 9:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1489508
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse