I need to calculate Worked Hours for an employee Using in(1) and out(2) sequence. In that i have to check following condition:
1. The In(1) should be followed by out(2) sequence. Suppose he swipes in and does not swipe out the worked hours should not be calculated for that in and out sequence. It has to show in time and out time value should be null and worked hours also should be null..
Right now am using following code to achieve this concept, but it doesn't work properly. Kindly help me
with cte as
(
select *, rn = row_number() over (partition by [EmpId] order by [trDate])
from @Conversion where [EmpId]=@EmpId
)
insert into @WorkedMinutes ( EmpId, trdate, InTime, OutTime, WorkedMinutes) select i.[EmpId],i.[trDate], i.[trTime] as InTime, o.[trTime] as OutTime, datediff(mi,i.[trTime],o.[trTime])as TotalMinutes
from cte i
left join cte o on i.[EmpId] = o.[EmpId]
and i.rn = o.rn - 1
where i.[InOut] = '1'
order by i.[EmpId],i.rn