; FROM AS_Record
UNION ALL
SELECT CustID, OpDT, CollDT, TermID
FROM orgRecords) rec) orgrec INNER JOIN
ProcRecords ON orgrec.CustID = ProcRecords.CustID AND
orgrec.[Day] = ProcRecords.ProcDT INNER JOIN
bc ON ProcRecords.grp_id = bc.grp_id AND ProcRecords.id = bc.id where (ProcRecords.ProcDT between @st and @et)
and bc.qd_qt=3 --既签到又签退处理
--rec视图,OrgRecords与ProcRecords表及bc表的联合查询
--正常签到
update procrecords set realstart=min_dt,StartStatus=0
from (
SELECT CustID, [Day], grp_id, id,min(opdt) as min_dt
FROM #rec
where opdt between DATEADD(mi, tq, PlanStart) and DATEADD(mi, cd, PlanStart)
GROUP BY CustID, [Day], grp_id, id
) m where m.custid=procrecords.custid and procrecords.procdt=m.[day]
and procrecords.grp_id=m.grp_id and procrecords.id=m.id
update procrecords set realend=max_dt,EndStatus=0
from (
SELECT CustID, [Day], grp_id, id,max(opdt) as max_dt
FROM #rec
where opdt between DATEADD(mi, zt, PlanEnd) and DATEADD(mi, zc, PlanEnd)
GROUP BY CustID, [Day], grp_id, id
) m where m.custid=procrecords.custid and procrecords.procdt=m.[day]
and procrecords.grp_id=m.grp_id and procrecords.id=m.id
--迟到处理
update procrecords set realstart=min_dt,StartStatus=1
from (
SELECT CustID, [Day], grp_id, id,min(opdt) as min_dt
FROM #rec
where opdt between DATEADD(mi, cd, PlanStart) and DATEADD(mi, zt, PlanEnd) and not exists
( select *
from (
SELECT CustID, [Day], grp_id, id
FROM #rec
where opdt between DATEADD(mi, tq, PlanStart) and DATEADD(mi, cd, PlanStart)
GROUP BY CustID, [Day], grp_id, id
) n where n.custid=#rec.custid and #rec.[day]=n.[day]
and #rec.grp_id=n.grp_id and #rec.id=n.id
)
GROUP BY CustID, [Day], grp_id, id
) m where m.custid=procrecords.custid and procrecords.procdt=m.[day]
and procrecords.grp_id=m.grp_id and procrecords.id=m.id
--早退处理
update procrecords set realend=max_dt,EndStatus=2
from (
SELECT CustID, [Day], grp_id, id,max(opdt) as max_dt
FROM #rec
where opdt between DATEADD(mi, cd, PlanStart) and DATEADD(mi, zt, PlanEnd) and not exists
( select *
from (
SELECT CustID, [Day], grp_id, id
FROM #rec
where opdt between DATEADD(mi, zt, PlanEnd) and DATEADD(mi, zc, PlanEnd)
GROUP BY CustID, [Day], grp_id, id
) n where n.custid=#rec.custid and #rec.[day]=n.[day]
and #rec.grp_id=n.grp_id and #rec.id=n.id
)
GROUP BY CustID, [Day], grp_id, id
) m where m.custid=procrecords.custid and procrecords.procdt=m.[day]
and procrecords.grp_id=m.grp_id and procrecords.id=m.id
--未刷处理
update procrecords set StartStatus=3 where (procdt between @st and @et) and StartStatus=''
update procrecords set EndStatus=3 where (procdt between @st and @et) and EndStatus=''
drop table #rec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO