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
2022-02-28T17:15:48.63+00:00

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.

178592-image.png
kalububu

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
Transact-SQL
Transact-SQL
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
    2022-03-01T02:18:49.193+00:00

    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,
    LiHong


    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
    2022-02-28T17:32:24.423+00:00
    ;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
    2022-02-28T20:31:41.913+00:00

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


  3. Naomi 7,361 Reputation points
    2022-02-28T21:37:30.063+00:00

    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