vendredi 27 novembre 2020

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

  • RaceA takes place on 2025-05-10 10:00
  • RaceA takes an average of 120 minutes to complete
  • I want my randomized "PredictCompletionTime" column to be somewhere between:

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,




Aucun commentaire:

Enregistrer un commentaire