Generating a Constrained Random Date–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

There have been lots of posts on the topic of generating random values, and some great articles. One of my favorites is Jeff Moden’s Generating Test Data: Part 1 – Generating Random Integers and Floats. Part 2 deals with dates, and that’s actually what I needed, but really I needed part 1.

In my situation, I was helping a customer generate some random data. They had filled a table, Customers, with some data.

2018-08-24 13_05_44-Microsoft Edge
The goal was to populate a child table with some data. The child table had a date column that was supposed to be between the Entered and Exit dates in the Customer table.

My update would have a join, obviously, and I can reference the enter and exit date, but how to get a date between them? My first thought was that I wanted a DATEADD() function. Something like this:

UPDATE ce
SET ce.EventTimeStamp = DATEADD( MINUTE, SomeRandomValue, c.CustomerExitedDateTime)), c.CustomerEnteredDateTime)
FROM   dbo.Customer AS c
INNER JOIN dbo.CustomerEvent AS ce
ON ce.CustomerID = c.CustomerID

The trick is what random value to use? If you look through Jeff’s article, you will see that the trick is to use a tally table and the NEWID() function. However, this doesn’t work:

 UPDATE ce
SET ce.EventTimeStamp = DATEADD( MINUTE, NEWID(), c.CustomerEnteredDateTime)
FROM   dbo.Customer AS c
INNER JOIN dbo.CustomerEvent AS ce
ON ce.CustomerID = c.CustomerID
;

What I need to do is convert the GUID to a number. In this case, I added CHECKSUM around it, again, as in Jeff’s article. Then use ABS() to enclose this to get all positive numbers.

 UPDATE ce
SET ce.EventTimeStamp = DATEADD( MINUTE, ABS(CHECKSUM((NEWID())))), c.CustomerEnteredDateTime)
FROM   dbo.Customer AS c
INNER JOIN dbo.CustomerEvent AS ce
ON ce.CustomerID = c.CustomerID
;

This gives me values, but they aren’t constrained. What I need to do is limit the upper random value so that the end time doesn’t exceed the Customer.CustomerExitDateTime for that row.

To do this, I can constraint a large set of numbers to some value with the modulo function. This will limit what values can appear. The basic script is this:

UPDATE ce
SET ce.EventTimeStamp = DATEADD( MINUTE, ABS(CHECKSUM((NEWID())))) % 10, c.CustomerEnteredDateTime)
FROM   dbo.Customer AS c
INNER JOIN dbo.CustomerEvent AS ce
ON ce.CustomerID = c.CustomerID
;

This would give me values between 1 and 0 minutes after the start time, but this doesn’t mean these values won’t be after the exit time. This is also an unrealistic window if most of the time the enter and exit times vary by hours.

What I did instead was to use the difference between the enter and exit times, with DATEDIFF() as my modulo function. That gives me:

WITH myTally (n)
AS
-- SQL Prompt formatting off
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
)
UPDATE ce
SET ce.EventTimeStamp = DATEADD( MINUTE, ABS(CHECKSUM((NEWID()))) % (DATEDIFF(MINUTE, c.CustomerEnteredDateTime, c.CustomerExitedDateTime)), c.CustomerEnteredDateTime)
FROM   dbo.Customer AS c
INNER JOIN dbo.CustomerEvent AS ce
ON ce.CustomerID = c.CustomerID
;

I run this, and I get the table updated with a random set of values.

2018-08-24 13_19_08-Microsoft Edge

SQLNewBlogger

This was a problem in my daily work. It was a customer, but it could easily be an internal query problem. I spent about 10 minutes grabbing screen shots and taking apart the query I’d built.

You can do this, too. Show us your mind working with the solutions you write in your own blog.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.