计算弹性工作日函数.txt

计算弹性工作日函数.txt /******************************************************************************* 功能:计算一个时间段内的迟到、早退、旷工、加班 参数: @BeginDate DateTime 开始时间 @EndDate DateTime 结束时间 算法: ,、根据允许迟到分钟数，判断是否迟到、更新迟到时间 ,、根据允许早退分钟数，判断是否早退、更新早退时间 ,、根据旷工开始时间，结合迟到、早退时间，判断是否早旷工、更新旷工时间 *******************************************************************************/ Declare @CALC_ShiftBaseDate DateTime /*班次基 准日期*/ Declare @CALC_EmptyAttendType UniqueIdentifier /*空出 勤类型*/ Declare @CALC_NormalShiftType UniqueIdentifier /*0001 正常班*/ Declare @CALC_FixedOTType UniqueIdentifier /*0002 固定加班*/ Declare @CALC_FixedOTExcType UniqueIdentifier /*0003 固定加班计异常*/ Declare @CALC_ContinueOTType UniqueIdentifier /*0004 直落加班*/ Declare @CALC_ContinueOTExcType UniqueIdentifier /*0005 直落加班计异常*/ Declare @CALC_NormalShift UniqueIdentifier /*0001 工作日*/ Declare @CALC_PostponedShift UniqueIdentifier /*0002 顺延*/ Declare @CALC_PublicHolidayShift UniqueIdentifier /*0003 公休息日*/ Declare @CALC_StatutoryHolidayShift UniqueIdentifier /*0004 法定假日*/ Declare @CALC_NoCardInfo UniqueIdentifier /*异常类 型中的缺卡ID*/ Set @CALC_ShiftBaseDate = '1900-01-10' Set @CALC_EmptyAttendType = '00000000-0000-0000-0000-000000000000' /*空出勤类型*/ Set @CALC_NormalShiftType = '55201C26-DF28-4959-8E47-0D1F157298CF' /*0001 正常班*/ Set @CALC_FixedOTType = '69CEAB4C-7694-4DB0-9EAC-14052A967411' /*0002 固定加班*/ Set @CALC_FixedOTExcType = '9E3CB1D7-D756-41DF-83E2-86AE2253DAD0' /*0003 固定加班计异常*/ Set @CALC_ContinueOTType = '2E571C22-9667-4AA3-9274-A4D699779B72' /*0004 直落加班*/ Set @CALC_ContinueOTExcType = '5BE9A64C-1FEF-42C6-8C38-907C4AFAC89C' /*0005 直落加班计异常*/ Set @CALC_NormalShift = '5749FB98-74C9-4C89-8128-CC2EDE6427E5' /*0001 工作日*/ Set @CALC_PostponedShift = '086A625A-ECCF-45EA-A586-91BE562D4936' /*0002 顺延*/ Set @CALC_PublicHolidayShift = '964F5BF5-C791-4773-BF2F-7174CA618932' /*0003 公休息日*/ Set @CALC_StatutoryHolidayShift = '3419050E-ED5C-4F7E-B3B4-3E4E7E296914' /*0004 法定假日*/ Set @CALC_NoCardInfo = 'B06F1569-BD92-4092-B8DD-EE6DF24C7757' /*缺卡*/ /*------------------- Start 计算迟到、早退、旷工等异常 ----------------------------------*/ --获取每个时间段迟到、早退、旷工分钟数，并放入临时中 Select CalcAttend.FEmpID,CalcAttend.FAttendDay, FAllowLate=Shift.FAllowLate, FAllowEarly=Shift.FAllowEarly, FAbsentStart=Shift.FAbsentStart, FAttendType1=Shift.FAttendType1,FAttendType2= Shift.FAttendType2, FAttendType3=Shift.FAttendType3, FAttendType4=Shift.FAttendType4, FAttendType5=Shift.FAttendType5, FDutyFrom1 = Case When CalcAttend.FDutyFrom1 Is Not Null Then CalcAttend.FDutyFrom1 End, FDutyTo1 = Case When CalcAttend.FDutyTo1 Is Not Null Then CalcAttend.FDutyTo1 End, FCheckIn1 = Shift.FCheckIn1, FCheckOut1 = Shift.FCheckOut1, FDutyFrom1 = CalcAttend.FDutyFrom1, FDutyTo1 = CalcAttend.FDutyTo1, FBreakTime1 = Shift.FBreakTime1, FLateMinute1 = Case When FDutyFrom1 Is Not Null Then DATEDIFF(minute , FCheckIn1 + FAttendDay - @CALC_ShiftBaseDate , FDutyFrom1) Else 0 End, FLateMinute2 = Case When FDutyFrom2 Is Not Null Then DATEDIFF(minute , FCheckIn2 + FAttendDay - @CALC_ShiftBaseDate , FDutyFrom2) Else 0 End, FLateMinute3 = Case When FDutyFrom3 Is Not Null Then DATEDIFF(minute , FCheckIn3 + FAttendDay - @CALC_ShiftBaseDate , FDutyFrom3) Else 0 End, FLateMinute4 = Case When FDutyFrom4 Is Not Null Then DATEDIFF(minute , FCheckIn4 + FAttendDay - @CALC_ShiftBaseDate , FDutyFrom4) Else 0 End, FLateMinute5 = Case When FDutyFrom5 Is Not Null Then DATEDIFF(minute , FCheckIn5 + FAttendDay - @CALC_ShiftBaseDate , FDutyFrom5) Else 0 End, FEarlyMinute1 = Case When FDutyTo1 Is Not Null Then DATEDIFF(minute ,FDutyTo1 , FCheckOut1 + FAttendDay - @CALC_ShiftBaseDate) Else 0 End, FEarlyMinute2 = Case When FDutyTo2 Is Not Null Then DATEDIFF(minute , FDutyTo2 , FCheckOut2 + FAttendDay - @CALC_ShiftBaseDate) Else 0 End, FEarlyMinute3 = Case When CalcAttend.FDutyTo3 Is Not Null Then DATEDIFF(minute , FDutyTo3 , FCheckOut3 + FAttendDay - @CALC_ShiftBaseDate) Else 0 End, FEarlyMinute4 = Case When FDutyTo4 Is Not Null Then DATEDIFF(minute , FDutyTo4 , FCheckOut4 + FAttendDay - @CALC_ShiftBaseDate) Else 0 End, FEarlyMinute5 = Case When FDutyTo5 Is Not Null Then DATEDIFF(minute , FDutyTo5 , FCheckOut5 + FAttendDay - @CALC_ShiftBaseDate) Else 0 End, FAbsentMinute1 = Case When FDutyFrom1 IS NULL OR FDutyTo1 IS NULL Then DATEDIFF(minute, FCheckIn1,FCheckOut1) Else 0 End, FAbsentMinute2 = Case When FDutyFrom2 IS NULL OR FDutyTo2 IS NULL Then DATEDIFF(minute, FCheckIn2, FCheckOut2) Else 0 End, FAbsentMinute3 = Case When FDutyFrom3 IS NULL OR FDutyTo3 IS NULL Then DATEDIFF(minute, FCheckIn3, FCheckOut3) Else 0 End, FAbsentMinute4 = Case When FDutyFrom4 IS NULL OR FDutyTo4 IS NULL Then DATEDIFF(minute,FCheckIn4,FCheckOut4) Else 0 End, FAbsentMinute5 = Case When FDutyFrom5 IS NULL OR FDutyTo5 IS NULL Then DATEDIFF(minute,FCheckIn5, FCheckOut5) Else 0 End, FStandTime1 = IsNull(DateDiff(Minute,FCheckin1,FCheckout1) - FBreakTime1-120, 0), FStandTime2 = IsNull(DateDiff(Minute,FCheckin2,FCheckout2) - FBreakTime2, 0), FStandTime3 = IsNull(DateDiff(Minute,FCheckin3,FCheckout3) - FBreakTime3, 0), FStandTime4 = IsNull(DateDiff(Minute,FCheckin4,FCheckout4) - FBreakTime4, 0), FStandTime5 = IsNull(DateDiff(Minute,FCheckin5,FCheckout5) - FBreakTime5, 0) Into #CALC_TempAbnormity From HR_ATS_EmpCalcAttend CalcAttend Inner join #Temp_EmpSelected EmpSelected On EmpSelected.FEmpID=CalcAttend.FEmpID Inner Join HR_ATS_ShiftInfo Shift ON Shift.FShiftID = CalcAttend.FShiftID Where CalcAttend.FAttendDay Between @BeginDate And @EndDate And CalcAttend.FStatus = 1 --修正旷工处理，当没有上班卡也没有下班卡的情况下的旷工， --如果小于旷工起始值，且大于迟到起始值，则认为是迟到; Update #CALC_TempAbnormity /*修正迟到*/ Set FLateMinute1 = Case When FAbsentMinute1 < FAbsentStart AND FLateMinute1=0 Then FAbsentMinute1 Else FLateMinute1 End, FLateMinute2 = Case When FAbsentMinute2 < FAbsentStart AND FLateMinute2=0 Then FAbsentMinute2 Else FLateMinute2 End, FLateMinute3 = Case When FAbsentMinute3 < FAbsentStart AND FLateMinute3=0 Then FAbsentMinute3 Else FLateMinute3 End, FLateMinute4 = Case When FAbsentMinute4 < FAbsentStart AND FAbsentMinute4>0 Then FAbsentMinute4 Else FLateMinute4 End, FLateMinute5 = Case When FAbsentMinute5 < FAbsentStart AND FAbsentMinute5>0 Then FAbsentMinute5 Else FLateMinute5 End /*修正异常，将异常时间为空或小于允许值的时间清零*/ Update #CALC_TempAbnormity /*修正迟到*/ Set FLateMinute1 = Case When FLateMinute1 > FAllowLate Then FLateMinute1-FAllowLate Else 0 End, FLateMinute2 = Case When FLateMinute2 > FAllowLate Then FLateMinute2 Else 0 End, FLateMinute3 = Case When FLateMinute3 > FAllowLate Then FLateMinute3 Else 0 End, FLateMinute4 = Case When FLateMinute4 > FAllowLate Then FLateMinute4 Else 0 End, FLateMinute5 = Case When FLateMinute5 > FAllowLate Then FLateMinute5 Else 0 End, /*修正早退*/ FEarlyMinute1 = Case When FDutyFrom1 >= FCheckIn1 +FAttendDay - @CALC_ShiftBaseDate And FDutyFrom1<=DATEADD(minute,FAllowLate,FCheckIn1 + FAttendDay - @CALC_ShiftBaseDate) And FDutyTo1 DATEADD(minute,FAllowLate,(FCheckIn1 + FAttendDay - @CALC_ShiftBaseDate)) And FDutyTo1 < FCheckOut1 + FAttendDay - @CALC_ShiftBaseDate Then 480 - DATEDIFF(minute,FBreakTime1,DATEDIFF(minute,FDutyFrom1,FDutyTo1))- (FLateMinute1-FAllowLate) Else 0 End, FEarlyMinute2 = Case When FEarlyMinute2 > FAllowEarly Then FEarlyMinute2 Else 0 End, FEarlyMinute3 = Case When FEarlyMinute3 > FAllowEarly Then FEarlyMinute3 Else 0 End, FEarlyMinute4 = Case When FEarlyMinute4 > FAllowEarly Then FEarlyMinute4 Else 0 End, FEarlyMinute5 = Case When FEarlyMinute5 > FAllowEarly Then FEarlyMinute5 Else 0 End, /*修正旷工*/ FAbsentMinute1 = Case When FAbsentMinute1 >= FAbsentStart Then 480 Else 0 End, FAbsentMinute2 = Case When FAbsentMinute2 >= FAbsentStart Then FAbsentMinute2 Else 0 End, FAbsentMinute3 = Case When FAbsentMinute3 >= FAbsentStart Then FAbsentMinute3 Else 0 End, FAbsentMinute4 = Case When FAbsentMinute4 >= FAbsentStart Then FAbsentMinute4 Else 0 End, FAbsentMinute5 = Case When FAbsentMinute5 >= FAbsentStart Then FAbsentMinute5 Else 0 End /* 修正迟到+早退之和大于旷工允许值的情况 设置 迟到分钟数1 = 0， 早退分钟数1 = 0 旷工分钟数1 = 迟到分钟数1 + 早退分钟数1 当 迟到分钟数1 + 早退分钟数1 >= 旷工允许值 下同 */ if(1 = 2) begin Update TempAbnormity Set FLateMinute1 = 0, FEarlyMinute1 = 0, FAbsentMinute1 = FAbsentMinute1 + FLateMinute1 + FEarlyMinute1 From #CALC_TempAbnormity TempAbnormity Where FLateMinute1 + FEarlyMinute1 >= FAbsentStart /*第二段*/ Update TempAbnormity Set FLateMinute2 = 0, FEarlyMinute2 = 0, FAbsentMinute2 = FAbsentMinute2 + FLateMinute2 + FEarlyMinute2 From #CALC_TempAbnormity TempAbnormity Where FLateMinute2 + FEarlyMinute2 >= FAbsentStart /*第三段*/ Update TempAbnormity Set FLateMinute3 = 0, FEarlyMinute3 = 0, FAbsentMinute3 = FAbsentMinute3 + FLateMinute3 + FEarlyMinute3 From #CALC_TempAbnormity TempAbnormity Where FLateMinute3 + FEarlyMinute3 >= FAbsentStart /*第四段*/ Update TempAbnormity Set FLateMinute4 = 0, FEarlyMinute4 = 0, FAbsentMinute4 = FAbsentMinute4 + FLateMinute4 + FEarlyMinute4 From #CALC_TempAbnormity TempAbnormity Where FLateMinute4 + FEarlyMinute4 >= FAbsentStart /*第五段*/ Update TempAbnormity Set FLateMinute5 = 0, FEarlyMinute5 = 0, FAbsentMinute5 = FAbsentMinute5 + FLateMinute5 + FEarlyMinute5 From #CALC_TempAbnormity TempAbnormity Where FLateMinute5 + FEarlyMinute5 >= FAbsentStart End Else Begin Update TempAbnormity Set FLateMinute1 =case when FLateMinute1 >= FAbsentStart then 0 else FLateMinute1 end, FEarlyMinute1 = case when FEarlyMinute1 >= FAbsentStart then 0 else FEarlyMinute1 end, FAbsentMinute1= case when FLateMinute1 >= FAbsentStart and FEarlyMinute1 < FAbsentStart then FAbsentMinute1 + FLateMinute1 when FEarlyMinute1 >= FAbsentStart and FLateMinute1 < FAbsentStart then FAbsentMinute1+ FEarlyMinute1 when FLateMinute1 >= FAbsentStart and FEarlyMinute1 >= FAbsentStart then FAbsentMinute1 + FLateMinute1+FEarlyMinute1 else 0 end From #CALC_TempAbnormity TempAbnormity Where FLateMinute1 >= FAbsentStart or FEarlyMinute1 >= FAbsentStart /*第二段*/ Update TempAbnormity Set FLateMinute2 =case when FLateMinute2 >= FAbsentStart then 0 else FLateMinute2 end, FEarlyMinute2 = case when FEarlyMinute2 >= FAbsentStart then 0 else FEarlyMinute2 end, FAbsentMinute2= case when FLateMinute2 >= FAbsentStart and FEarlyMinute2 < FAbsentStart then FAbsentMinute2 + FLateMinute2 when FEarlyMinute2 >= FAbsentStart and FLateMinute2 < FAbsentStart then FAbsentMinute2+ FEarlyMinute2 when FLateMinute2 >= FAbsentStart and FEarlyMinute2 >= FAbsentStart then FAbsentMinute2 + FLateMinute2+FEarlyMinute2 else 0 end From #CALC_TempAbnormity TempAbnormity Where FLateMinute2 >= FAbsentStart or FEarlyMinute2 >= FAbsentStart /*第三段*/ Update TempAbnormity Set FLateMinute3 =case when FLateMinute3 >= FAbsentStart then 0 else FLateMinute3 end, FEarlyMinute3 = case when FEarlyMinute3 >= FAbsentStart then 0 else FEarlyMinute3 end, FAbsentMinute3= case when FLateMinute3 >= FAbsentStart and FEarlyMinute3 < FAbsentStart then FAbsentMinute3 + FLateMinute3 when FEarlyMinute3 >= FAbsentStart and FLateMinute3 < FAbsentStart then FAbsentMinute3+ FEarlyMinute3 when FLateMinute3 >= FAbsentStart and FEarlyMinute3 >= FAbsentStart then FAbsentMinute3 + FLateMinute3+FEarlyMinute3 else 0 end From #CALC_TempAbnormity TempAbnormity Where FLateMinute3 >= FAbsentStart or FEarlyMinute3 >= FAbsentStart /*第四段*/ Update TempAbnormity Set FLateMinute4 =case when FLateMinute4 >= FAbsentStart then 0 else FLateMinute4 end, FEarlyMinute4 = case when FEarlyMinute4 >= FAbsentStart then 0 else FEarlyMinute4 end, FAbsentMinute4= case when FLateMinute4 >= FAbsentStart and FEarlyMinute4 < FAbsentStart then FAbsentMinute4 + FLateMinute4 when FEarlyMinute4 >= FAbsentStart and FLateMinute4 < FAbsentStart then FAbsentMinute4+ FEarlyMinute4 when FLateMinute4 >= FAbsentStart and FEarlyMinute4 >= FAbsentStart then FAbsentMinute4 + FLateMinute4+FEarlyMinute4 else 0 end From #CALC_TempAbnormity TempAbnormity Where FLateMinute4 >= FAbsentStart or FEarlyMinute4 >= FAbsentStart /*第五段*/ Update TempAbnormity Set FLateMinute5 =case when FLateMinute5 >= FAbsentStart then 0 else FLateMinute5 end, FEarlyMinute5 = case when FEarlyMinute5 >= FAbsentStart then 0 else FEarlyMinute5 end, FAbsentMinute5= case when FLateMinute5 >= FAbsentStart and FEarlyMinute5 < FAbsentStart then FAbsentMinute5 + FLateMinute5 when FEarlyMinute5 >= FAbsentStart and FLateMinute5 < FAbsentStart then FAbsentMinute5+ FEarlyMinute5 when FLateMinute5 >= FAbsentStart and FEarlyMinute5 >= FAbsentStart then FAbsentMinute5 + FLateMinute5+FEarlyMinute5 else 0 end From #CALC_TempAbnormity TempAbnormity Where FLateMinute5>= FAbsentStart or FEarlyMinute5 >= FAbsentStart End /*当异常时间大于班次段的

标准

excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载

时长时，将标准时长取为异常时间*/ /*修正异常，将异常时间为空或小于允许值的时间清零*/ Update #CALC_TempAbnormity /*修正迟到*/ Set FLateMinute1 = Case When FLateMinute1 < FStandTime1 Then FLateMinute1 Else FStandTime1 End, FLateMinute2 = Case When FLateMinute2 < FStandTime2 Then FLateMinute2 Else FStandTime2 End, FLateMinute3 = Case When FLateMinute3 < FStandTime3 Then FLateMinute3 Else FStandTime3 End, FLateMinute4 = Case When FLateMinute4 < FStandTime4 Then FLateMinute4 Else FStandTime4 End, FLateMinute5 = Case When FLateMinute5 < FStandTime5 Then FLateMinute5 Else FStandTime5 End, /*修正早退*/ FEarlyMinute1 = Case When FEarlyMinute1 < FStandTime1 Then FEarlyMinute1 Else FStandTime1 End, FEarlyMinute2 = Case When FEarlyMinute2 < FStandTime2 Then FEarlyMinute2 Else FStandTime2 End, FEarlyMinute3 = Case When FEarlyMinute3 < FStandTime3 Then FEarlyMinute3 Else FStandTime3 End, FEarlyMinute4 = Case When FEarlyMinute4 < FStandTime4 Then FEarlyMinute4 Else FStandTime4 End, FEarlyMinute5 = Case When FEarlyMinute5 < FStandTime5 Then FEarlyMinute5 Else FStandTime5 End, /*修正旷工*/ FAbsentMinute1 = Case When FAbsentMinute1 < FStandTime1 Then FAbsentMinute1 Else FStandTime1 End, FAbsentMinute2 = Case When FAbsentMinute2 < FStandTime2 Then FAbsentMinute2 Else FStandTime2 End, FAbsentMinute3 = Case When FAbsentMinute3 < FStandTime3 Then FAbsentMinute3 Else FStandTime3 End, FAbsentMinute4 = Case When FAbsentMinute4 < FStandTime4 Then FAbsentMinute4 Else FStandTime4 End, FAbsentMinute5 = Case When FAbsentMinute5 < FStandTime5 Then FAbsentMinute5 Else FStandTime5 End /* ----------------------------------------------------------- 更新各考勤段固定加班是否记录异常情况, 即将固定加班，固定直落加班不记异常的考勤段，不记录异常*/ /*第一段*/ Update TempAbnormity Set FLateMinute1 = 0, FEarlyMinute1 = 0, FAbsentMinute1 = 0 From #CALC_TempAbnormity TempAbnormity Where FAttendType1 In (@CALC_FixedOTType, @CALC_ContinueOTType) /*第二段*/ Update TempAbnormity Set FLateMinute2 = 0, FEarlyMinute2 = 0, FAbsentMinute2 = 0 From #CALC_TempAbnormity TempAbnormity Where FAttendType2 In (@CALC_FixedOTType, @CALC_ContinueOTType) /*第三段*/ Update TempAbnormity Set FLateMinute3 = 0, FEarlyMinute3 = 0, FAbsentMinute3 = 0 From #CALC_TempAbnormity TempAbnormity Where FAttendType3 In (@CALC_FixedOTType, @CALC_ContinueOTType) /*第四段*/ Update TempAbnormity Set FLateMinute4 = 0, FEarlyMinute4 = 0, FAbsentMinute4 = 0 From #CALC_TempAbnormity TempAbnormity Where FAttendType4 In (@CALC_FixedOTType, @CALC_ContinueOTType) /*第五段*/ Update TempAbnormity Set FLateMinute5 = 0, FEarlyMinute5 = 0, FAbsentMinute5 = 0 From #CALC_TempAbnormity TempAbnormity Where FAttendType5 In (@CALC_FixedOTType, @CALC_ContinueOTType) Create Clustered Index idx_TempAbnormity_AeendDayEmpID ON #CALC_TempAbnormity(FAttendDay,FEmpID) /*更新职员考勤计算信息*/ Update Attend Set Attend.FAbsentHour1 = Convert(Decimal(6,2),TempAbnormity.FAbsentMinute1 * 1.0 / 60), Attend.FAbsentHour2 = Convert(Decimal(6,2),TempAbnormity.FAbsentMinute2 * 1.0 / 60), Attend.FAbsentHour3 = Convert(Decimal(6,2),TempAbnormity.FAbsentMinute3 * 1.0 / 60), Attend.FAbsentHour4 = Convert(Decimal(6,2),TempAbnormity.FAbsentMinute4 * 1.0 / 60), Attend.FAbsentHour5 = Convert(Decimal(6,2),TempAbnormity.FAbsentMinute5 * 1.0 / 60), Attend.FLateMinute1 = TempAbnormity.FLateMinute1, Attend.FLateMinute2 = TempAbnormity.FLateMinute2, Attend.FLateMinute3 = TempAbnormity.FLateMinute3, Attend.FLateMinute4 = TempAbnormity.FLateMinute4, Attend.FLateMinute5 = TempAbnormity.FLateMinute5, Attend.FEarlyMinute1 = TempAbnormity.FEarlyMinute1, Attend.FEarlyMinute2 = TempAbnormity.FEarlyMinute2, Attend.FEarlyMinute3 = TempAbnormity.FEarlyMinute3, Attend.FEarlyMinute4 = TempAbnormity.FEarlyMinute4, Attend.FEarlyMinute5 = TempAbnormity.FEarlyMinute5 From HR_ATS_EmpCalcAttend Attend Inner Join #CALC_TempAbnormity TempAbnormity ON Attend.FAttendDay = TempAbnormity.FAttendDay And Attend.FEmpID = TempAbnormity.FEmpID Where Attend.FAttendDay Between @BeginDate And @EndDate And Attend.FStatus=1 /*删除临时表*/ Drop Table #CALC_TempAbnormity /*------------------- End 计算迟到、早退、旷工等异常 ----------------------------------*/ /*-------------------------- 缺卡处理:如果考勤打卡次数(只包有效班次段的打卡记录)小于班次打卡次数，考勤 异常为缺卡 */ Create Table #CALC_TempExpTimes ( FAttendID int, FAttendTime int, FShiftTime int ) Insert Into #CALC_TempExpTimes(FAttendID, FAttendTime,FShiftTime) Select FAttendID = Attend.FID, FAttendTime=((Case When shift.FAttendType1 <> @CALC_EmptyAttendType AND FDutyFrom1 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType1 <> @CALC_EmptyAttendType AND FDutyTo1 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType2 <> @CALC_EmptyAttendType AND FDutyFrom2 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType2 <> @CALC_EmptyAttendType AND FDutyTo2 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType3 <> @CALC_EmptyAttendType AND FDutyFrom3 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType3 <> @CALC_EmptyAttendType AND FDutyTo3 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType4 <> @CALC_EmptyAttendType AND FDutyFrom4 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType4 <> @CALC_EmptyAttendType AND FDutyTo4 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType5 <> @CALC_EmptyAttendType AND FDutyFrom5 IS NOT NULL then 1 else 0 End) + (Case When shift.FAttendType5 <> @CALC_EmptyAttendType AND FDutyTo5 IS NOT NULL then 1 else 0 End)), FShiftTime=((Case When FCheckIn1 is null then 0 else 1 End) + (Case When FCheckOut1 is null then 0 else 1 End) + (Case When FCheckIn2 is null then 0 else 1 End) + (Case When FCheckOut2 is null then 0 else 1 End) + (Case When FCheckIn3 is null then 0 else 1 End) + (Case When FCheckOut3 is null then 0 else 1 End) + (Case When FCheckIn4 is null then 0 else 1 End) + (Case When FCheckOut4 is null then 0 else 1 End) + (Case When FCheckIn5 is null then 0 else 1 End) + (Case When FCheckOut5 is null then 0 else 1 End)) From HR_ATS_EmpCalcAttend Attend Inner Join HR_ATS_ShiftInfo Shift On Shift.FShiftID=Attend.FShiftID Inner join #Temp_EmpSelected EmpSelected On EmpSelected.FEmpID=Attend.FEmpID Where FAttendDay Between @BeginDate And @EndDate And Attend.FStatus=1 Update Attend Set FExcpType = Case When FAttendTime < FShiftTime Then @CALC_NoCardInfo /*缺卡异常*/ When FAttendTime = FShiftTime And FExcpType=@CALC_NoCardInfo Then Null Else Attend.FExcpType End From HR_ATS_EmpCalcAttend Attend Inner Join #CALC_TempExpTimes Times On Attend.FID = Times.FAttendID And Attend.FAttendDay Between @BeginDate And @EndDate And Attend.FStatus=1 Drop Table #CALC_TempExpTimes /* -------------------------------------- 计算实际工时，实际工时 = 标准工时 */ Update Attend Set FFactLabor=Info.FLaborHour From HR_ATS_EmpCalcAttend Attend Inner join #Temp_EmpSelected EmpSelected On EmpSelected.FEmpID=Attend.FEmpID Inner Join HR_ATS_ShiftInfo Info On Info.FShiftID = Attend.FShiftID Where FAttendDay Between @BeginDate And @EndDate And Attend.FStatus=1