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