(
@st datetime, --起始日期
@et datetime --终止日期
)
AS
--fqyz一卡通系统kp考勤子系统，对原始记录进行处理的SQL语句
SELECT orgrec.*, ProcRecords.PlanWorkID, ProcRecords.PlanStart, ProcRecords.RealStart,
ProcRecords.StartStatus, ProcRecords.PlanEnd, ProcRecords.RealEnd,
ProcRecords.EndStatus into #rec
FROM (SELECT *, DATEADD([day], DATEDIFF([day], 0, OpDT), 0) AS [Day]
FROM OrgRecords) orgrec INNER JOIN
ProcRecords ON orgrec.CustID = ProcRecords.CustID AND
orgrec.[Day] = ProcRecords.ProcDT where ProcRecords.ProcDT between @st and @et
--rec视图,OrgRecords与ProcRecords表的联合查询，关联条件是日期(day=procdt)与客户号(custid)
/*
SELECT CustID, [Day],planworkid, MIN(OpDT) AS min_dt,max(opdt) as max_dt
FROM rec
--WHERE ....
GROUP BY CustID, [Day],planworkid
--获取某个人某天某一班次的第一次刷卡记录与最后一次刷卡记录,条件是关键 */
SELECT CustID, [Day], PlanWorkID, MIN(OpDT) AS min_dt into #min_dt
FROM #rec rec
WHERE (OpDT <= DATEADD(mi, 5, PlanStart)) AND (OpDT >= ISNULL
((SELECT dateadd(mi, 5, planend)
FROM procrecords AS p
WHERE p.custid = rec.custid AND p.procdt = rec.day AND
p.planworkid = rec.planworkid - 1), DATEADD([Day], DATEDIFF([Day], 0,
OpDT), 0) + ' 00:00:00'))
GROUP BY CustID, [Day], PlanWorkID
--获取某个人某天某一班次的第一次刷卡记录.允许迟到5分钟
SELECT CustID, [Day], PlanWorkID, Min(OpDT) AS min_dt,max(opdt) as max_dt into #max_dt
FROM #rec rec
GROUP BY CustID, [Day], PlanWorkID
--
--select * from #min_dt
update
procrecords set
realstart=m.min_dt,StartStatus=0,realend=m.min_dt,EndStatus=0 from
#min_dt as m where procrecords.custid=m.custid and
procrecords.procdt=m.day
and procrecords.planworkid=m.planworkid --只签到不签退.正常签到处理
update procrecords set
realstart=m.min_dt,StartStatus=1,realend=m.max_dt,EndStatus=1 from
#max_dt as m where procrecords.custid=m.custid and
procrecords.procdt=m.day
and procrecords.planworkid=m.planworkid
and m.min_dt>DATEADD(mi, 5, PlanStart) and m.max_dt< DATEADD(mi,
-5, PlanEnd) --迟到处理
update procrecords set StartStatus=4,EndStatus=4 where (procdt between @st and @et) and StartStatus='' and EndStatus=''
--旷工处理
drop table #min_dt
drop table #max_dt
drop table #rec
