Click here to Skip to main content
15,915,603 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted

1 solution

Try this. Here 'end' is your out time & 'start' is your in time. variable 'total' is number of hours worked.


TimeSpan ts = DateTime.Parse(end) - DateTime.Parse(start);
            float total = ts.Hours;
 
Share this answer
 
Comments
Selvi797 1-Feb-14 0:03am    
I want that should be done in sql server itself and i have to check my above condition also using in and out sequence.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900