Adding minutes to the current date and create a new row with the spill over minutes

C1 | Start_Time      | Minutes
---+-----------------+---------
I1 | 8/6/17 23:50 PM |  40

I am using SQL Server, and this is how my table looks like. Start_Time of the machine is '8/6/17 23:50 PM' continues to run for 40 minutes. By the time when machine stops, it will be next day. In this case the end time is 8/7/17. Now I need to separate the spill over time and make the next day as new entry.

The result should be like

C1  |   Time            | Minutes
----+-------------------+---------
I1  |   8/6/17 11:50 PM |  10
I1  |   8/7/17 12:00 AM |  30

The date and minutes must be separated into different rows.

1 answer

  • answered 2018-05-16 05:44 Tim Biegeleisen

    You may try an insert query which checks if the difference between the Minutes and the remaining minutes of a given Start_Time on that day is greater than zero. If so, then do an insert with that difference.

    INSERT INTO yourTable (C1, Start_Time, Minutes)
    SELECT
        C1,
        DATEADD(d, 1, DATEDIFF(d, 0, Start_Time)),
        Minutes - DATEDIFF(mi, Start_Time, DATEADD(d, 1, DATEDIFF(d, 0, Start_Time)))
    FROM yourTable
    WHERE
        Minutes - DATEDIFF(mi, Start_Time, DATEADD(d, 1, DATEDIFF(d, 0, Start_Time))) > 0;
    

    Follow the link below for a demo which shows that the insert logic is working correctly.

    Demo

    Edit:

    I just realized that you will also need an update query to adjust the remaining minutes for each record affected by the above insert. You may use this:

    UPDATE yourTable
    SET Minutes = DATEDIFF(mi, Start_Time, DATEADD(d, 1, DATEDIFF(d, 0, Start_Time)))
    WHERE
        Minutes - DATEDIFF(mi, Start_Time, DATEADD(d, 1, DATEDIFF(d, 0, Start_Time))) > 0;