如何通过看过去签到时间来区分白班和夜班? [英] How to distinguish between day and night shifts by looking at past sign in times?
本文介绍了如何通过看过去签到时间来区分白班和夜班?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
目前，报告使用预定义的时间.但有时员工会超时工作，他们会潜入下一次轮班报告.如何修改报告查询以查看过去的登录时间以避免这种情况?在 TIMEATT 列中，1 是入口，2 是出口.浅蓝色突出显示正确的条目，黄色表示不正确的条目.
Currently, the report uses predefined times. But sometimes employees overstay their shift and they creep into the next shift report. How do I modify the report query to look at the past sign in times to avoid this? In the TIMEATT column 1 is entry and 2 is exit. Light blue highlights the correct entries and yellow signify incorrect entries.
29 日的白班报告有完成，简"从夜班开始的入场时间
Day shift report for 29th has 'Done, Jane's' entry time from night shift
29 日的夜班报告从白班开始有做，琼斯"的进入时间
Night shift report for 29th has 'Do, Jone's' entry time from day shift
下面的查询使用与屏幕截图中相同的数据创建一个临时表.
Below query creates a temp table with the same data as in the screenshots.
CREATE TABLE #temptable ( [Company] nvarchar(60), [ID] int, [NAME] nvarchar(130), [TIMEATT] int, [Time_CST] datetime )
INSERT INTO #temptable
values
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-28T18:00:27' ),
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-28T18:00:28' ), --Sometimes people tap their card twice
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-29T06:00:55' ),
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-29T06:01:55' ),
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-29T06:04:55' ),
( N'Company Ltd.', 11111, N'Done, Jane', 1, N'2019-04-29T18:00:27' ),
( N'Company Ltd.', 11111, N'Done, Jane', 2, N'2019-04-30T06:13:55' ),
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T06:20:17' ),
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T06:47:12' ),
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T10:33:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T18:05:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-29T18:06:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-29T18:09:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 1, N'2019-04-30T06:05:33' ),
( N'Company Ltd.', 22222, N'Do, Jone', 2, N'2019-04-30T16:05:33' )
-- Test table
-- 1 is entry and 2 is exit. Sometimes people double swipe their cards which results in two 1 entries.
select * from #temptable as T
--Report start
set deadlock_priority low;
declare @shift varchar(10) = 'night'; --Option to switch between day and night
declare @reportdate datetime = '2019-04-29'; --Date to be ran
declare @starttime datetime;
declare @endtime datetime;
select @starttime = (case
when @shift = 'day' then
convert(datetime, @reportdate) + cast('04:00:00.000' as datetime)
when @shift = 'night' then
convert(datetime, @reportdate) + cast('16:00:00.000' as datetime)
end
);
select @endtime = (case
when @shift = 'day' then
convert(datetime, @reportdate) + cast('23:59:59.000' as datetime)
when @shift = 'night' then
convert(datetime, dateadd(d, 1, @reportdate)) + cast('11:59:59.000' as datetime)
end
);
select Company
, NAME
, EmpID
, startTime
, endTime
, sum(datediff(second, startTime, endTime) / 3600.0) as HrsWorked
from
( -- sub query to get matching exit time for each entry if it exists
select Company
, NAME
, ID as EmpID
, Time_CST as startTime
, lead(Time_CST, 1, null) over (partition by NAME order by Time_CST) as endTime
, TIMEATT
, Time_CST
from
( -- subquery to exclude duplicate records
select *
from
(
select *
from
( -- subquery to identify records to ignore
select Company
, NAME
, ID
, TIMEATT
, Time_CST
, case lead(TIMEATT, 1, 0) over (partition by NAME order by Time_CST)
when TIMEATT then
1
else
0
end as Exclude
from #temptable
) a
where Exclude = 0
) t
) n
) z
where TIMEATT = 1 -- filter so left column is always entry time.
and startTime >= @starttime
and endTime <= @endtime
--and Company in (@contractornames)
group by z.Company
, z.NAME
, z.EmpID
, z.startTime
, z.endTime
order by Company
, NAME
, startTime
--DROP TABLE #temptable
推荐答案
所以 Chris 的回答给了我一个很好的工作格式.我正在检查是否有人在凌晨 4 点至下午 12 点之间进入，而在中午 12 点至晚上 7 点之间退出将被视为白班，其他人将被视为夜班.
So Chris's answer gave me a good format to work with. I am checking to see if anyone has an entry within 4am-12pm and an exit with 12pm-7pm will be consdered day shift and anybody else will be night shift.
set deadlock_priority low;
declare @shift varchar(10) = 'day' --Option to switch between day and night
declare @reportdate datetime = '2019-04-29' --Date to be ran
declare @starttime datetime
declare @endtime datetime
select @starttime = (case
when @shift = 'day' then
convert(datetime, @reportdate) + cast('04:00:00.000' as datetime)
when @shift = 'night' then
convert(datetime, @reportdate) + cast('16:00:00.000' as datetime)
end
)
select @endtime = (case
when @shift = 'day' then
convert(datetime, @reportdate) + cast('23:59:59.000' as datetime)
when @shift = 'night' then
convert(datetime, dateadd(d, 1, @reportdate)) + cast('11:59:59.000' as datetime)
end
)
;with NoDoubles
as (select Company
, NAME
, ID
, TIMEATT
, Time_CST
, case
when lead(TIMEATT, 1, 0) over (partition by NAME order by Time_CST) = TIMEATT then
1
/* Allow for 10 minute grace period for swipes to be excluded */
when lead(Time_CST, 1, 0) over (partition by NAME order by Time_CST) = '1900-01-01 00:00:00.000' then
0
when lead(Time_CST, 1, 0) over (partition by NAME order by Time_CST) <= dateadd(minute, 1, Time_CST) then
1
else
0
end as Exclude
from temptable)
, DayShiftEmpIds
as (select distinct
(case
when count(entertimes.ID) > 0 then
entertimes.ID
end
) as id
, 'day' as shift
--,case when count(entertimes.id)>0 then 'day' else 'night' end as [shift]
from temptable entertimes
, temptable exittimes
where entertimes.ID = exittimes.ID
and ((
(
entertimes.Time_CST >= @reportdate + cast('04:00:00' as datetime)
and entertimes.Time_CST <= @reportdate + cast('11:59:00' as datetime)
and entertimes.TIMEATT = 1
)
and
(
exittimes.Time_CST >= @reportdate + cast('12:00:00' as datetime)
and exittimes.Time_CST <= @reportdate + cast('19:59:00' as datetime)
and exittimes.TIMEATT = 2
)
)
)
group by entertimes.ID)
, NightShiftEmpIds
as (select distinct
ID
, 'night' as shift
from temptable as VCCIOT
where not exists
(
select id from DayShiftEmpIds where DayShiftEmpIds.ID = VCCIOT.ID
))
, AllEmpIdsWithShift
as (select *
from
(
select id
, shift
from DayShiftEmpIds
union
select ID
, shift
from NightShiftEmpIds
) as alldata
where alldata.shift = @shift)
, InOut
as (select Company
, NAME
, NoDoubles.ID as ID
, Time_CST as startTime
, lead(Time_CST, 1, null) over (partition by NAME order by Time_CST) as endTime
, TIMEATT
, Time_CST
from NoDoubles
inner join AllEmpIdsWithShift
on AllEmpIdsWithShift.id = NoDoubles.ID
where Exclude = 0)
select Company
, NAME
, ID
, startTime
, endTime
, sum(datediff(second, startTime, endTime) / 3600.0) as HrsWorked
from InOut
where TIMEATT = 1 -- filter so left column is always entry time.
and startTime >= @starttime
and endTime <= @endtime
group by Company
, NAME
, ID
, startTime
, endTime
order by Company
, NAME
, startTime;
这篇关于如何通过看过去签到时间来区分白班和夜班?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
