Random Time Distribution using SQL Server

I have a table in SQL Server (current data)

Total Over Time of EmpId 1001 in January-2020 is 3hrs 24 min

I want to distribute/add 3hrs 24min randomly between 1-jan-20 to 31-jan-20 to OutTime column

(expected result)

There are 200+ employees and different employees have different over time data (stored in another table) How to I write a query to update the OutTime of those employee who has overtime

Answer

i write this code for your problems this code work for one employee you can expand this code for all employee with one cursor or while.

this select is for generate sample data into temp table

SELECT *
INTO #DATA
FROM (
VALUES
(1001,'2020-01-01','09:14' , '06:45'),
(1001,'2020-01-02','08:20' , '06:15'),
(1001,'2020-01-03','07:14' , '06:20'),
(1001,'2020-01-04','09:30' , '07:00'),
(1001,'2020-01-05','09:20' , '06:30'),
(1001,'2020-01-06','09:14' , '06:40'),
(1001,'2020-01-07','08:45' , '06:10'),
(1001,'2020-01-08','08:30' , '07:45'),
(1001,'2020-01-09','08:14' , '07:15')
) AS F(EMPID , PURCHDATE,INTIME,OUTETIME)


-- the time must be add in random value is 3:24
DECLARE @AddedTime TIME = '3:24'

with this code convert your time as minute becare full about second in your time if you have second you will be add in this code and change all minute date part to second

DECLARE @AddedTimeAsMinute INT = DATEPART(HOUR , @AddedTime) * 60 + DATEPART(MINUTE , @AddedTime)
---------------------------------------------------------
PRINT @AddedMinute
---------------------------------------------------------

this part is for get min and max date and differente beween them for chose a random day between them.

DECLARE @DiffDate INT
DECLARE @StartDate DATE = '2020-01-01',@EndDate DATE = '2020-01-09'
SELECT @DiffDate = DATEDIFF(DAY,@StartDate ,@EndDate )

after that you can select one day as random and add a random minute to outtime

WHILE @AddedTimeAsMinute > 0
BEGIN

    DECLARE @AddMinute INT= (SELECT FLOOR(RAND()*(@AddedTimeAsMinute - 1))+1)

    SELECT *,CAST(DATEADD(MINUTE,@AddMinute,OUTETIME) AS TIME) NEWTIME
    FROM  #DATA 
    WHERE DAY(PURCHDATE) = FLOOR(RAND()*(@DiffDate - 1 + 1)) +1
    AND EMPID = 1001

    SET @AddedTimeAsMinute = @AddedTimeAsMinute - @AddMinute

END
DROP TABLE #DATA

you can put the core of while loop into a cursor for any employee data