如何计算在sql server 2012中使用进出顺序的员工的工作时间？ [英] How to calculate Worked hours for an employee using in and out sequence in sql server 2012 ?

问题描述

我需要计算员工的工作时间使用（1）和（2）顺序。因为我必须检查以下条件：

1. In（1）后面应该是out（2）序列。假设他滑入并且没有刷出工作时间，不应计算进出的顺序。它必须在时间和时间显示值应该为null并且工作时间也应该为null ..



现在我正在使用以下代码来实现这个概念，但是它不能正常工作。请帮助我



用cte作为

（

select *，rn = row_number（）over（partition by [EmpId]订购[trDate]）
来自@Conversion的
，其中[EmpId] = @ EmpId

）

插入@WorkedMinutes（ EmpId，trdate，InTime，OutTime，WorkedMinutes）选择i。[EmpId]，i。[trDate]，i。[trTime]作为InTime，o。[trTime]作为OutTime，dateiff（mi，i。[trTime]，o 。[trTime]）作为TotalMinutes

来自cte i

离开加入cte o on i。[EmpId] = o。[EmpId]

和i.rn = o.rn - 1

其中i。[InOut] ='1'

由i。[EmpId]订购，i.rn

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

推荐答案

试试这个。这里'结束'是你的外出时间& '开始'就是你的时间。变量'total'是工作小时数。

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;

这篇关于如何计算在sql server 2012中使用进出顺序的员工的工作时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！