how to add one minutes to the next row if clock out HH:MM is the same as clock in HH:MM in the next row using sql

zenbaba weldemariam 41 Reputation points

Hi Friends
Please I need help on this requirement.
I have data in table as shown in the screenshot below
I need to add one minutes to the next row(record) if Hour column is the same as in the next row for the same Employee ID and Date using sql

Any kind of help is highly appreciated.


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points

    Hi @zenbaba weldemariam
    If the datatype of [Hour] column is 'Time',then try this code:

    ;WITH CTE AS  
     SELECT * ,DATEADD(MINUTE, 1, [Hour]) AS NewHour,LAG([Hour])OVER(ORDER BY Employee_id,[Date], [Hour],Record_Type DESC)AS LAG_Hour  
     FROM #TEST  
    SELECT * FROM CTE WHERE Record_Type='In Punch' AND [Hour]=LAG_Hour --Check first, then change to update  
    --UPDATE CTE SET [Hour]=NewHour  

    Best regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

3 additional answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    ;with cte as (select *, out.[hour] as [outhour] from dbo.Labor l
    cross apply (select top (1)  *  FROM dbo.Labor l2 where l2.employee_id = l.employee_id and l2.[date]=l.[date]
    and datepart(hour, l2.[Hour]) = datepart(hour, l.[hour]) 
    and [Record Type Indicator] = 'Out Punch'
    order by l2.[Hour] desc ) out
    where l.[Record Type Indicator] = 'In Punch')
    select * from cte -- verify you got correct data, if so, change select to
    update cte set [Hour] = dateadd(minite, 1, [Hour])

  2. Naomi 7,361 Reputation points

    You're missing FROM between select top(1) and dbo.punchcard as l2

  3. Naomi 7,361 Reputation points

    Ok, try then

    ;with cte as (select *, dateadd(minute, 1, [Hour]) as NewHour,
    row_number() over (partition by Employee_id, [Date], [Hour] order by
    [Record Type Indicator] as Rn
    from myTable)
    select * from cte where Rn = 1 -- these are the rows that going to be updated
    -- if satisfied, change to
    update cte set [Hour] = NewHour