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

Question

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.

kalububu

Answer 1

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.

Answer 2

;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])

Answer 3

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

Answer 4

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