CODEWITHSUNDEEP
sqlsql-server
Alex Gurskiy
Alex Gurskiy

Reputation: 273

Generate random date for some period

I have a temp table in my database with rates and period:

Rate | Period
-----+--------
3    |  Week
1    |  Month
2    |  Month
1    |  Week
3    |  Year
1    |  Monh
1    |  Month
1    |  Month
1    |  Month
1    |  Month
1    |  Month
6    |  Year
2    |  Month

I need to generate random dates for 2014 year. For example '6 Year' should be 6 random dates for 2014 year. 1 Week should be random date for each week in 2014 year. May someone help me?

So, this is my function. But dates is not almost correct. Maybe anyone may find an error?

CREATE FUNCTION GenerateDate(@date_from date, @Rate int, @Period nvarchar(50))
RETURNS @LIST TABLE(item date)

BEGIN
    DECLARE @i int, @j int, @d int
    DECLARE @date date
    IF (@Period LIKE 'Y%')
    BEGIN
    SET @i=0
    WHILE (@i < @Rate)
    BEGIN
        SET @date = DATEADD (day, dbo.Amount(0,364), @date_from)
        IF @date NOT IN (SELECT * FROM @LIST)
        BEGIN
            INSERT INTO @LIST VALUES (@date)
            SET @i=@i+1
        END
    END
END
IF (@Period LIKE 'M%')
BEGIN
    SET @i=1
    WHILE (@i <= 12)
    BEGIN
        SET @j=0
        SET @d = CASE @i
                    WHEN 2 THEN 28
                    WHEN 4 THEN 30
                    WHEN 6 THEN 30
                    WHEN 9 THEN 30
                    WHEN 11 THEN 30 
                    ELSE 31
                 END
        WHILE (@j < @Rate)
        BEGIN
            SET @date = DATEADD (day, dbo.Amount(0,@d-1), @date_from)
            IF @date NOT IN (SELECT * FROM @LIST)
            BEGIN
                INSERT INTO @LIST VALUES (@date)
                SET @j=@j+1
            END
        END
        SET @date_from = DATEADD (month, 1, @date_from)
        SET @i=@i+1
    END
END
IF (@Period LIKE 'W%')
BEGIN
    SET @i = 1
    WHILE (@i <= 52)
    BEGIN
        SET @j=0
        WHILE (@j < @Rate)
        BEGIN
            SET @date = DATEADD (day, dbo.Amount(0,6), @date_from)
            IF @date NOT IN (SELECT * FROM @LIST)
            BEGIN
                INSERT INTO @LIST VALUES (@date)
                SET @j=@j+1
            END
        END
        SET @date_from = DATEADD (week, 1, @date_from)
        SET @i=@i+1
    END
END
RETURN
END

CREATE FUNCTION Amount(@AmountMin float, @AmountMax float)
RETURNS float
AS
    BEGIN
        DECLARE @Amount float = (SELECT new_rand FROM RandomNumbers)*(@AmountMax-@AmountMin) + @AmountMin
        RETURN @Amount
    END
GO

CREATE VIEW RandomNumbers
AS
SELECT cast( RAND(CHECKSUM(NEWID()))*1000 AS INT) AS new_rand 
GO

Upvotes: 1

Views: 1600

Answers (5)

gdoghel
gdoghel

Reputation: 21

I used this to get a random date from current month:

SELECT FROM_UNIXTIME(FLOOR( RAND() * (UNIX_TIMESTAMP(DATE(LAST_DAY(now())))-UNIX_TIMESTAMP(DATE(DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY)))) + UNIX_TIMESTAMP(DATE(DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY)))))

Explanation:

  • Get first and last day of the period.
  • Convert all to Unix timestamps.
  • Get a random number between those timestamp.
  • Convert back to time format.

Upvotes: 1

gvee
gvee

Reputation: 17161

Proof of concept using a calendar table!

DECLARE @year   int     = 2014
      , @rate   int     = 3
      , @period char(5) = 'Month'

; WITH this_year AS (
  SELECT the_date
       , CASE @period
           WHEN 'Year'  THEN DatePart(year    , the_date)
           WHEN 'Month' THEN DatePart(month   , the_date)
           WHEN 'Week'  THEN DatePart(iso_week, the_date)
           WHEN 'Day'   THEN DatePart(day     , the_date)
         END As groups
       , NewID() As random
  FROM   dbo.calendar
  WHERE  DatePart(year, the_date) = @year
)
, x AS (
  SELECT the_date
       , Row_Number() OVER (PARTITION BY groups ORDER BY random) As sequence
  FROM   this_year
)
SELECT the_date
FROM   x
WHERE  sequence <= @rate
;

You can find out how to build a calendar table all over the net. Here's one I made earlier.

Improved the code to join to a table...

DECLARE @t table (
   rate   int
 , period char(5)
 , UNIQUE (period, rate)
);

INSERT INTO @t (rate, period)
VALUES (3, 'Year' ) --    3
     , (2, 'Month') --   24
     , (1, 'Week' ) --   52 (or 53, depending on the year)
                    -- = 79 (or 80)
;

; WITH this_year AS (
  SELECT calendar.the_date
       , t.rate
       , t.period
       , CASE t.period
           WHEN 'Year'  THEN DatePart(year    , the_date)
           WHEN 'Month' THEN DatePart(month   , the_date)
           WHEN 'Week'  THEN DatePart(iso_week, the_date)
           WHEN 'Day'   THEN DatePart(day     , the_date)
         END As groups
       , NewID() As random
  FROM   dbo.calendar
   CROSS
    JOIN @t As t
  WHERE  DatePart(year, calendar.the_date) = Year(Current_Timestamp)
)
, x AS (
  SELECT the_date
       , rate
       , period
       , groups
       , Row_Number() OVER (PARTITION BY period, rate, groups ORDER BY random) As sequence
  FROM   this_year
)
SELECT *
FROM   x
WHERE  sequence <= rate
;

Upvotes: 1

Kartic
Kartic

Reputation: 2985

To get 6 different dates in year 2014, you can try something like below -

DECLARE @Counter INT
DECLARE @MaxDateInterval INT
DECLARE @TmpDate DATE
DECLARE @NoOfDate INT
DECLARE @ResultDatesForYr TABLE (TmpDate DATE)

SET @Counter = 0
SET @NoOfDate = 6
SET @TmpDate = '2014-01-01'
SET @MaxDateInterval = FLOOR((365 - @NoOfDate)/@NoOfDate)

WHILE (@Counter < @NoOfDate)
BEGIN   
    SELECT @TmpDate = DATEADD(DAY, (SELECT (1+FLOOR(@MaxDateInterval*RAND()))), @TmpDate)
    INSERT INTO @ResultDatesForYr(TmpDate) VALUES (@TmpDate)
    SET @Counter = @Counter+1
END

SELECT * FROM @ResultDatesForYr

You can write a function that will accept no of dates required and year

Upvotes: 3

Kevin Cook
Kevin Cook

Reputation: 1932

Lets work on getting a random date for a week: First we get the length of time we are shooting for

DATEDIFF(MINUTE, 0, DATEADD(WEEK, 1, 0))

This returns 10080 as the max number of minutes in a week.

This code gets a random minute within a week range starting from now.

SELECT DATEADD(MINUTE, RAND() * 10080, GETDATE())

You can easily change this to use days, instead but I thought a datetime would be more interesting for an example.

Upvotes: 1

Pranav Shah
Pranav Shah

Reputation: 99

For example if you want to generate a random date between 2009-12-25 and 2009-12-28 you write

select '2009-12-25' + interval rand()*3 day

For me I wanted a value between 2008 and 2009 (one year: 60 seconds * 60 minutes * 24 hours * 365 days = 31536000). Because the Unix timestamp doesn’t support fractions your need to round the value to an int. (floor or round the value).

SELECT FROM_UNIXTIME(
UNIX_TIMESTAMP('2008-01-01 01:00:00')+FLOOR(RAND()*31536000)
);

Upvotes: -1

Related Questions