我如何让员工在首次打卡或银泰之间出现转变 [英] How Do I Get Employee Shift Which Lies Between First Punch Or Intime

问题描述

我有三班倒，时间不足。

Hi,

I have three shifts with below timings.

                 Intime         Outtime

General Shift :  10:00 AM       06:00 PM
First Shift   :  07:00 AM       03:00 PM
Night Shift   :  23:00 PM       07:00 AM

现在，假设是否有员工在上午10:00，上午10:30打了一拳，那么它应该是给我一般班次。



如果任何员工在23：30,23：45,00：30等时候打了一拳，那么它应该返回夜班。我试过下面的查询。

Now , Suppose if any employee has punch at 10:00 AM, 10:30 AM then it should give me General Shift.

If any employee has punch at 23:30, 23:45,00:30 etc.. then it should return Night Shift. I tried below query for that.

select aPKShift,  shShiftInTime, shShiftOutTime
from amasterShift
WHERE
(CONVERT(Time,shShiftInTime) >= convert(time,'10:00:00')
and
CONVERT(Time,shShiftOutTime) <= convert(time,'10:00:00'))

但它给我错误的转变

But its giving me wrong shifts like this

Night         23:00:00.0000000    07:00:00.0000000
Third         23:00:00.0000000    07:00:00.0000000
Sixth Shift   21:00:00.0000000    06:00:00.0000000

请为上述查询提供一些建议

Please provide some suggestions for the above mentioned query

推荐答案

将转移时间放入表格并连接到主班次表



可接受的时间范围，如下所示

Put you shift timing into a table and make a join to your master shift table

with acceptable timerange, as given below

Create Table #masterShift(UserIn int,shShiftInTime datetime,shShiftOutTime datetime)


insert #masterShift select 1,'Nov 17 2014 07:00','Nov 17 2014 15:00'
insert #masterShift select 1,'Nov 17 2014 10:00','Nov 17 2014 18:00'
insert #masterShift select 1,'Nov 17 2014 23:00','Nov 18 2014 07:00'



Create Table #shiftTiming(Shift varchar(15),shShiftInTime time,shShiftOutTime time)


insert into #shiftTiming select 'General Shift','10:00 AM','06:00 PM'
insert into #shiftTiming select 'First Shift','07:00 AM','03:00 PM'
insert into #shiftTiming select 'Night Shift','23:00 PM','07:00 AM'

select *,Dateadd(HH,2,tym.shShiftInTime)
from
#masterShift sht
Full Outer Join #shiftTiming tym
On Convert(time,sht.shShiftInTime)>=tym.shShiftInTime
and Convert(time,sht.shShiftInTime)<=Dateadd(MI,59,tym.shShiftInTime)

Go

insert #masterShift select 1,'Nov 18 2014 07:30','Nov 18 2014 15:00'
insert #masterShift select 1,'Nov 18 2014 10:30','Nov 18 2014 18:00'
insert #masterShift select 1,'Nov 18 2014 23:30','Nov 19 2014 07:00'


select *
from
#masterShift sht
Join #shiftTiming tym On Convert(time,sht.shShiftInTime)>=tym.shShiftInTime and Convert(time,sht.shShiftInTime)<=Dateadd(MI,59,tym.shShiftInTime)

这里给出了59分钟的时差，而不是sson你这次会增加你的时间w生病了，从0开始，所以要用它来获得所需的结果。

here time difference of 59 minutes is given, Not as sson you will increase this time your time will change and start with 0 , so work with that to get you required result.

这篇关于我如何让员工在首次打卡或银泰之间出现转变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！