CODEWITHSUNDEEP
sql-serverdatabasedaterandomsql-update
thatsweetguy
thatsweetguy

Reputation: 35

Random dates without duplicates based on the value of other columns

I have a temp table called #RandomDates that looks like this in SQL Server:

╔════╦═════════════╦══════════╦══════════════════╦════════════════════════════════╦═══════════════════════╗
║ ID ║ Description ║ RaceType ║ RaceStartTime    ║ AverageCompletionTimeInMinutes ║ PredictCompletionTime ║
╠════╬═════════════╬══════════╬══════════════════╬════════════════════════════════╬═══════════════════════╣
║ 1  ║ Player1     ║ RaceA    ║ 2025-05-10 10:00 ║ 120                            ║ NULL                  ║
╠════╬═════════════╬══════════╬══════════════════╬════════════════════════════════╬═══════════════════════╣
║ 2  ║ Player2     ║ RaceA    ║ 2025-05-12 17:00 ║ 120                            ║ NULL                  ║
╠════╬═════════════╬══════════╬══════════════════╬════════════════════════════════╬═══════════════════════╣
║ 3  ║ Player3     ║ RaceC    ║ 2025-08-12 08:15 ║ 60                             ║ NULL                  ║
╠════╬═════════════╬══════════╬══════════════════╬════════════════════════════════╬═══════════════════════╣
║ 5  ║ Player4     ║ RaceY    ║ 2025-08-29 16:00 ║ 10                             ║ NULL                  ║
╠════╬═════════════╬══════════╬══════════════════╬════════════════════════════════╬═══════════════════════╣
║ 6  ║ Player4     ║ RaceY    ║ 2025-08-30 21:00 ║ 10                             ║ NULL                  ║
╚════╩═════════════╩══════════╩══════════════════╩════════════════════════════════╩═══════════════════════╝

I want to update the column "PredictCompletionTime" with random dates however I need them to be based on the values of columns "RaceStartTime" and "AverageCompletionTimeInMinutes".

Example for ID = 1

RaceStartTime + AverageCompletionTimeInMinutes + RANDOMLY add OR deduct a small amount of MINUTES and SECONDS ( lets say between 5 to 10 minutes )

So valid dates for this example could be:

2025-05-10 12:07:20

2025-05-10 11:59:40

I have tried doing this with RAND()* but for some reason my "PredictCompletonTime" column keeps getting updated with duplicated values for each RaceType.

Thanks in advance,

Upvotes: 0

Views: 55

Answers (1)

eshirvana
eshirvana

Reputation: 24603

here is an example, so random will create a random number between @MinTime and AverageCompletionTimeInMinutes for each row in second and will add to RaceStartTime :

DECLARE @MinTime int = 300 -- in second

UPDATE #tablename
SET PredictCompletionTime =  DATEADD(SECOND ,ROUND(RAND() * (AverageCompletionTimeInMinutes*60 - @MinTime) , 0),RaceStartTime )
FROM #tablename

Upvotes: 1

Related Questions