请问有人会分析存储过程吗？这个存储过程是我公司的，计算加班的时候少于半个小时就不会计算，希望能改成计算每一分钟。

Question

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

 

ALTER            procedure [dbo].[spGetEmployeeShiftByDate]

/*

select * from orgnization where orgcode like '005%'
select * from workdayrecord where staffno='21741' and datediff(month,fdate,'2009-12-1')=0
select * from workmonth where staffno='30921' and fdate='2009-10-1'
update workmonth set finishflag=0 where staffno='31901' and fdate='2009-9-1'
declare @date smalldatetime,@mDate datetime
set @date='2009-12-1'
set @mDate='2009-12-31'
declare @employeeID uniqueidentifier
select @employeeID=employeeID from employees where staffno='21741'
exec spGetEmployeeShiftByDate @employeeID,@date,@mDate,0

*/
@employeeID uniqueidentifier, --GUID
@date smalldatetime,  --开始日期
@eDate smalldatetime,  --结束日期
@eatFlag bit
as

set nocount on
if not exists(select 1 from employees where employeeid=@employeeID)
 return
set @date = dateAdd(day,-datePart(day,@date)+1,@date)

if datediff(month,getdate(),@date)>0
 return
declare @staffNo varchar(10),  --工号
 @Gender varchar(2),
 @maxDate datetime, --最大日期
 @WX  decimal(10,2), --无薪
 @WorkFlag bit,  --是否不计考勤
 @DutyDate smalldatetime, --入职日期
 @DimissionDate smallDatetime, --离职日期
 @SalaryMode varchar(3),  --薪制
 @OnDuty datetime,  --上班时间
 @OffDuty datetime,  --下班时间
 @OrgCode varchar(30),  --组织机构
 @SalaryFlag tinyint , --工资核算方法
 @ShiftOffDuty smalldatetime,  --班次时间段1下班时间
 @ShiftOnDuty smalldatetime,     --班次时间段2上班时间
 @DutyLevel int,
 @kcc decimal(18,2),
 @defaultShift varchar(10), --默认班次
 @Baoan bit,
 @txMin smalldatetime,
 @txMax smalldatetime,
 @workstation nvarchar(10)

set @eDate = dateadd(day,-1,dateAdd(month,1,@date))

--取工号等个人信息
select
@staffNo=StaffNo,@DutyDate=DutyDate,
@WorkFlag=WorkFlag,
@DimissionDate=b.DimissionDate,
@SalaryMode=SalaryMode,
@OrgCode=orgCode,
@Gender=gender,
@SalaryFlag=isNull(SalaryFlag,0),
@DutyLevel = cast(DutyLevelCode as int),
@workstation=workstation,
@Baoan=case when (workstation in ('保安','保安班长','宿管员','清洁工','保安队长') or (orgcode like '005004001%' and workstation in ('电工','电梯工','行政助理'))) and salarymode='001' then 1 else 0 end
from Employees a
left join EmployeeDimission b on a.EmployeeID=b.EmployeeID
where a.EmployeeID=@employeeId
if(@orgcode like '010%')
 set @baoan=0
select @defaultShift=isnull(fid,'A01') from shift where orgcode=left(@orgCode,3) and isdefault=1

--------------如果已经审核月报，直接返回
if exists(select 1 from workmonth where staffno=@staffno and datediff(month,fdate,@date)=0 and finishflag=1)
 return
--判断是否不计工资

if @SalaryFlag = 1
begin
 --如果不计工资则删掉考勤资料
 delete from WorkDayRecord
 where StaffNo=@StaffNo and datediff(month,Fdate,@date)=0

 delete from WorkMonth
 where StaffNo=@StaffNo and datediff(month,FDate,@date)=0
 
 return

end

 

-----------------计算餐费---------------
declare @EatSum decimal(18,2)
select
@EatSum = isNull(
sum(case when FType ='1早餐' then 1 else 0 end) * 0.6 +
sum(case when FType ='2中餐' then 1 else 0 end) * 3.0 +
sum(case when FType ='3晚餐' then 1 else 0 end) * 3.0 +
sum(case when FType ='2中餐' and FKind ='加餐' then 1 else 0 end) * 2,0)
from eatList a
left join employees b on a.staffno=b.staffno
where datediff(month,a.FDate,@Date)=0 and a.StaffNo = @staffNo
-----------------------------------------------------------------------
if(@eatflag=1)
begin
 if exists(select 1 from workmonth where datediff(month,fdate,@date)=0 and staffno=@staffno)
  update workmonth set eattotal=@eatsum where datediff(month,fdate,@date)=0 and staffno=@staffno
 return
end

--不计考勤(全勤)
if @WorkFlag=1
begin
 declare @st decimal(18,2),@rt decimal(18,2),@kDate smalldatetime
 if(getdate()<@eDate)
  set @kDate=convert(char(10),getdate(),120)
 else
  set @kDate=@eDate
 select @kDate
 select @st=dbo.GetMonthDayByStaffNo(@StaffNo,@date),@rt=dbo.fGetEmployeeRealTime(@StaffNo,@kDate)
 delete from WorkDayRecord where StaffNo=@StaffNo and datediff(month,FDate,@date)=0
 if exists (select 1 from WorkMonth where StaffNo=@Staffno and datediff(month,FDate,@date)=0)
 delete from WorkMonth where StaffNo=@Staffno and datediff(month,FDate,@date)=0
 declare @sql varchar(1000)
 select @sql=[dbo].[fGetEmployeeWorkMonth](@staffno,@kDate)
 print @sql
 exec(@sql)
 
 update workmonth
 set eattotal=@eatsum
 where staffno=@staffno and datediff(month,fdate,@date)=0
 --select @wx=shouldtime-realtime-isnull(sj,0)-isnull(nj,0)-isnull(hj,0) from workmonth where staffno=@staffno and fdate=@date
 --------统计出差

 select @kcc=sum(case wholeday when 0 then hours when 1 then 4 when 2 then 4 when 3 then 8 end) from employeeleave where holidaytype='003' and staffno=@StaffNo and datediff(month,fdate,@date)=0 and status=3
 update workmonth set cc=isnull(@kcc,0)/8 where staffno=@staffno and datediff(month,fdate,@date)=0
 return
end

-------------如果月考勤数据为导入数据则只更新应勤及餐费----------------------
if exists (select 1 from workMonth where StaffNo=@staffNo and datediff(month,FDate,@date)=0 and isNull(ImportFlag,0)=1)
begin
 --删掉日报表
 /*
 delete WorkDayRecord
 where StaffNo=@StaffNo and datediff(month,FDate,@date)=0
 */
 update WorkMonth
 set ShouldTime = dbo.GetMonthDayByStaffNo(@StaffNo,@date),
 EatTotal = @EatSum
 where StaffNo=@StaffNo and datediff(month,Fdate,@date)=0

 if @SalaryMode='002'
 begin
  --计算无薪
  set @WX =0
  while @date<=@edate
  begin
   if not exists (select 1 from PublicHoliday h
    inner join Employees e on e.OrgCode like h.OrgCode + '%'
    and e.EmployeeID=@EmployeeID and e.SalaryMode=h.SalaryMode
    and h.Gender like '%' + e.Gender + '%'
    and HolidayDate=@date and h.DayPart='0'
    inner join OverTimeType t on h.HolidayType=t.FID
   )
    and ( @date<@DutyDate or @date>=@DimissionDate )
    set @WX = @WX + 1

   set @date=dateadd(day,1,@date)
  end

  set @date=dateadd(day,-1,@date)

  update WorkMonth
  set
  WX=@WX
  where StaffNo=@StaffNo and datediff(month,Fdate,@date)=0
 end
 

 
 
 return
end

--------------------------------------------------------------------------
declare

@ShiftID varchar(10), --班次编号
@OnDuty1 datetime, --上班1
@OffDuty1 datetime, --下班1
@OnDuty2 datetime, --上班2
@OffDuty2 datetime, --下班2
@OnDuty3 datetime, --上班3
@OffDuty3 datetime, --下班3
@OnDuty4 datetime, --上班4
@OffDuty4 datetime, --下班4
@holidayFlag bit,  --放假标志

@ShouldTime decimal(10,2), --应勤
@RealTime decimal(10,4), --实勤
@LateEarly decimal(10,2), --迟到早退
@LateNum        smallint,  --迟到早退次数
@Neglect decimal(10,2), --旷工

@YeBan  int, --夜班
@TongXiao int, --通宵
@BK  smallint, --补卡
@HolidayType varchar(20),
@LeaveDayPart smallint, --请假时间段(1上午,2下午,3全天)
@LeaveHours decimal(18,2), --请假时数
@LeaveDayType varchar(10), --请假类别

@ygfj  decimal(10,2), --因公放假
@DayPart varchar(5),
@OnDutyBal int,
@OffDutyBal int,
@StartTime smalldatetime, --加班开始时间
@EndTime smalldatetime, --加班结束时间
@Hours  decimal(18,2), --加班时数
@OWType  varchar(3), --加班类别
@OWTime  int,

@OWMul  decimal(5,2), --加班倍数
@OW1  decimal(18,2), --1.5倍加班
@OW2  decimal(18,2), --2倍加班
@OW3  decimal(18,2), --3倍加班
@OWApply decimal(18,2), --加班申请时数
@temp  int,
@SJ  decimal(10,2), --事假
@BJ  decimal(10,2), --病假
@CC  decimal(10,2), --出差
@NJ  decimal(10,2), --年假
@HJ  decimal(10,2), --婚假
@CJ  decimal(10,2), --产假
@PCJ  decimal(10,2), --陪产假
@GSJ  decimal(10,2), --工伤假
@SaJ  decimal(10,2), --丧假
@BRJ  decimal(18,2), --哺乳假
@AdjustRest decimal(10,2), --调休
@AdjustRestS decimal(10,2), --班次中调休时间
@WXS  decimal(10,2), --班次中无薪时间
@OShiftID varchar(10), --加班班次
@RepairFlag bit,  --是否补班
@NeglectOW decimal(10,2), --“必须”加班产生的旷工
@OPeriodTime smallint --加班班次时间段
set @LateNum=0
--declare @tDate smalldatetime,@pDate smalldatetime
select @maxDate = LastDate from WorkMonth where staffno=@staffno and datediff(month,fdate,dateadd(month,-1,@date))=0

if @maxDate is Null
 set @maxDate = dateAdd(day,-1,@date)

while @date<=@eDate
begin

 if exists(select 1 from workdayrecord where staffno=@staffno and fdate=@date and updateflag=1)
  select @defaultshift=shiftid from workdayrecord where staffno=@staffno and fdate=@date
 set @RepairFlag=0
 select @RepairFlag=1 from EmployeeLeave a inner join WorkRepair b on a.LeaveID=b.LeaveID where a.staffNo=@staffno and b.FDate=@date and a.status=3
 
 --取昨天、今天及明天的打卡时间
 select @StaffNo as 工号,FDate as 打卡时间 into #t1 from
 (select FDate from WorkTime
 where StaffNo=@StaffNo and abs(datediff(day,@date,FDate)) in (1,0)
 and FDate >@maxDate
 union
 select FDate from WOrkTimeAdd
 where StaffNo=@StaffNo and Status=3
 and abs(datediff(day,@date,FDate)) in (1,0)
 and FDate >@maxDate
 ) a
 order by 打卡时间

 --判断当天是否放假
 select @ShiftID=null,@DayPart=null, @HolidayType=null,@OWMul=null
 while not exists(select 1 from publicholiday where datediff(month,HolidayDate,@date)=0 and OrgCode= @OrgCode and (left(Gender,1)=@Gender or right(Gender,1)=@Gender) and SalaryMode=@SalaryMode)
 begin
  if(len(@orgcode)<=3)
   break;
  set @orgcode=left(@orgcode,len(@orgcode)-3)
  
 end
 
  select @DayPart=h.DayPart,@HolidayType=HolidayType,@OWMul=FMultiple from PublicHoliday h
  inner join Employees e on e.OrgCode like h.OrgCode + '%' and e.EmployeeID=@EmployeeID and e.SalaryMode=h.SalaryMode
  and h.Gender like '%' + e.Gender + '%'
  and HolidayDate=@date
  inner join OverTimeType t on h.HolidayType=t.FID
  where h.orgcode=@Orgcode
 
 select @OWApply=0,@OW1=0,@OW2=0,@OW3=0,@LateEarly=0 ,@LateNum=0,@OWTime=0,@RealTime=0 ,@Neglect=0 ,@NeglectOW=0,@BK=0 ,@YeBan=0 ,@WX=0,@TongXiao=0
 select @AdjustRest =0,@SJ=0,@BJ=0,@CC=0,@NJ=0,@HJ=0,@CJ=0,@PCJ=0,@GSJ=0,@SaJ=0,@BRJ=0,@YGFJ=0,@LeaveDayPart=0,@LeaveHours=0,@LeaveDayType=null
 select @OnDuty = null,@OffDuty = null,@OnDuty1=null,@OnDuty2=null,@OnDuty3=null,@OffDuty1=null,@OffDuty2=null,@OffDuty3=null

 set @OWMul=isNull(@OWMul,1.5)
 --如果当天放假，且不是无薪假 

  
 if (@DayPart='0' and @HolidayType<>'004') 
 and not exists (select 1 from EmployeeLeave a left join WorkRepair b on a.LeaveID=b.LeaveID where a.status=3 and a.Staffno=@Staffno and a.HolidayType='011' and a.WholeDay=3 and b.FDate=@date and a.FDate>='2008-6-1')
 begin
  
  
  if @DutyDate>@date
  begin
   if @HolidayType='001'
   begin
    
    if exists (select 1 from WorkDayRecord where Staffno=@Staffno and FDate=@Date)
     update workDayRecord set ShouldTime=8,RealTime=0,WX=8,OverWork1 =0,OverWork2=0,Overwork3=0,Neglect=0,CC=@Hours,BJ=0,SJ=0,HJ=0,Saj=0,GSJ=0,NJ=0,CJ=0,PCJ=0,BK=0,YeBan=0,TongXiao=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=0 from workDayRecord where Staffno=@Staffno and FDate=@Date
    else
     insert into workDayRecord (StaffNo,Fdate,ShouldTime,RealTime,WX)values(@StaffNo,@Date,8,0,8)
   end
   else
    delete from workDayRecord  where Staffno=@Staffno and FDate=@Date
   goto goon
  end
  --保安的加班-----------------------------------------
  if exists(select 1 from employees where staffno=@staffno and (workstation in ('保安','保安班长','宿管员','清洁工','保安队长') or (orgcode like '005004001%' and workstation in ('电工','电梯工','行政助理'))) and salarymode='001') and @orgcode not like '010%'
  begin
   select @ShiftID=ShiftID from WorkDayRecord where FDate=@Date and StaffNo=@StaffNo and UpdateFlag=1

   create table #owt2(
   ShiftID nvarchar(10),PeriodTime smallint,AheadTime int,AbsenceTimeA int,上班时间 datetime,
   AbsenceTimeL int,下班时间 datetime,DelayTime int,OWTime int,AdjustRest int,WX int)
   
   --班次信息,如果更新过班次,则以设置的班次为准
   
   if isNull(@ShiftID,'') <> ''
   begin
    insert into #owt2
    select
    ParentID,
    PeriodTime,
    AheadTime,
    datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeA,
    --AbsenceTimeA,
    dateadd(day,OnDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OnDutyTime as smalldatetime)) as 上班时间,
    datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeL,
    --AbsenceTimeL,
    dateadd(day,OffDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OffDutyTime as smalldatetime)) as 下班时间,
    DelayTime,OWTime,AdjustRest,WX
    from ShiftDetail where ParentID=@ShiftID
   end
   else
   begin --获取该员工所有班次
    
    
    insert into #owt2
    select
    ShiftID,
    PeriodTime,
    AheadTime,
    datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeA,
    --AbsenceTimeA,
    dateadd(day,OnDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OnDutyTime as smalldatetime)) as 上班时间,
    datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeL,
    --AbsenceTimeL,
    dateadd(day,OffDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OffDutyTime as smalldatetime)) as 下班时间,
    DelayTime,OWTime,AdjustRest,WX
    from EmployeeShift a
    inner join ShiftDetail b on a.ShiftID=b.ParentID
    where a.EmployeeID=@EmployeeID
   end
   
   select
   a.打卡时间,
   b.ShiftID as 班次编号,
   b.PeriodTime as 时间段,
   isNull(b.OWTime,0) as 加班,
   isNull(b.AdjustRest,0) as 调休,
   isNull(b.WX,0) as 无薪,
   cast(datediff(second,打卡时间,b.上班时间) as decimal(10,2))/60 as 上班时间差,
   cast(datediff(second,b.下班时间,打卡时间) as decimal(10,2))/60 as 下班时间差
   into #owt3
   from #t1 a ,#owt2 b
   where
   cast(datediff(second,a.打卡时间,b.上班时间) as decimal(10,2))/60 between AbsenceTimeA*-1 and AheadTime
   or cast(datediff(second,b.下班时间,a.打卡时间) as decimal(10,2))/60 between AbsenceTimeL*-1 and DelayTime
  
   select top 1 @shiftid=班次编号
    from(
    select 班次编号,时间段,min(abs(case when 上班时间差<0 then 上班时间差-50 else 上班时间差 end)) as 上班时间差,min(abs(case when 下班时间差<0 then 下班时间差-50 else 下班时间差 end)) as 下班时间差
    from #owt3
    group by 班次编号,时间段)a group by 班次编号
    order by sum(上班时间差+下班时间差)
   
   --没有找到匹配的班次,则取默认班次
   IF @ShiftID is null
   begin
    select top 1 @ShiftID=ShiftID from EmployeeShift where EmployeeID=@EmployeeID
    if @ShiftID is null
     set @ShiftID=@defaultshift
   end
   
   --置空
   select @OnDuty=null,@OffDuty=null,@OnDuty1=null,@OffDuty1=null,@OnDuty2=null,@OffDuty2=null,@OnDuty3=null,@OffDuty3=null,
   @ShouldTime=0, @OWTime=0,@StartTime=null

   
    

Answer 1

   --取上下班时间
  if @shiftID is not null
  begin
   select PeriodTime,cast(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))-RestTime as decimal(10,2))/60 as 应勤,restTime as 休息,
   AdjustRest as 调休,WX as 无薪,OWTime as 加班 into #owt4
   from ShiftDetail where ParentID=@ShiftID

   -------班次的时间段
   declare @owPeriodCount smallint,@j int
   select @j=0,@owPeriodCount=count(ParentID) from ShiftDetail where ParentID=@ShiftID
   
   while @j<@owPeriodCount
   begin
    select @j=@j+1,@OnDutyBal=0,@OffDutyBal=0,@OnDuty=null,@OffDuty=null,@OWTime=0,@WXS=0,@AdjustRestS=0

    if exists (select 1 from #owt2 where ShiftID=@ShiftID and PeriodTime=1 and 上班时间>getdate())
    begin
     
     select @WX = @WX + 应勤 from #owt4 where PeriodTime=@j
     continue
    end
    
    --取上班时间
    
    select top 1 @OnDuty=打卡时间,@OnDutyBal=上班时间差,@temp=加班,@AdjustRestS=调休,@WXS = 无薪 from #owt3 a
    inner join #owt2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
    where b.ShiftID=@shiftID and a.时间段=@j and 上班时间差 between b.absenceTimeA*-1 and b.AheadTime  and 打卡时间>isNull(@maxDate,@date)
    order by 上班时间差 desc--abs(上班时间差)

    if exists (select 1 from #owt2 where ShiftID=@ShiftID and PeriodTime=@j and 下班时间>getdate())
    begin
     select @WX = @WX + 应勤 from #owt4 where PeriodTime=@j
     continue
    end
    
    --取下班时间
  
    if (@OnDuty is not null)
    begin
     
     select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #owt3 a
     inner join #owt2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
     and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) and 打卡时间>@OnDuty
     where b.ShiftID=@shiftID and a.时间段=@j
     order by 下班时间差
    end
    else

     begin
     
     if isNull(@LeaveHours,0)<(select datediff(minute,上班时间,下班时间) from #owt2 where ShiftID=@ShiftID and PeriodTime=@j)
     begin
      
      select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #owt3 a
      inner join #owt2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
      and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) 
      where b.ShiftID=@shiftID and a.时间段=@j
      order by 下班时间差
      
     end
     end
    

 
   
    if @DayPart=cast(@j as varchar(1)) and @OnDuty is not null and @OffDuty is not null
    begin
     set @temp = datediff(minute ,@OnDuty,@OffDuty)
    end

    if @OffDuty is not null or @OnDuty is not null
     set @maxDate=isNull(@OffDuty,@OnDuty)
    if @j=1
     select @OnDuty1=@OnDuty,@OffDuty1=@OffDuty
    if @j=2
     select @OnDuty2=@OnDuty,@OffDuty2=@OffDuty
    if @j=3
     select @OnDuty3=@OnDuty,@OffDuty3=@OffDuty

   end

   if object_id('tempdb..#owt4') is not null
   drop table #owt4
  end
   if object_id('tempdb..#owt2') is not null
   drop table #owt2
   if object_id('tempdb..#owt3') is not null
   drop table #owt3
  --通宵及夜班 
   
   if(@offduty3 is not null)
   begin
    set @txMax=@offduty3
    set @txMin=@Onduty1
   end
   else if(@offduty2 is not null)
   begin
    set @txMax=@offduty2
    set @txMin=@Onduty1
   end
   else
   begin
    set @txmax=@offduty1
    set @txMin=@Onduty1
   end
   if(left(@orgcode,3)<>'002')
   begin
    --if(@txMin<dateadd(day,1,@date)+' 00:00:00' and @txMax>=dateadd(day,1,@date)+' 02:00:00')
    if @txMax>=dateadd(day,1,@date)+' 02:00:00'
     set @TongXiao=1
    else
     set @TongXiao=0
    if(@TongXiao=0)
    begin
     --if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 00:00:00')
     if @txMax>=dateadd(day,1,@date)+' 00:00:00'
     set @YeBan=1
    else
     set @YeBan=0
    end
    
    
   end
   else
   begin
    if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 06:30:00')
     set @TongXiao=1
    else
     set @TongXiao=0
    if(@TongXiao=0)
    begin
     if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 00:00:00')
      set @YeBan=1
     else
      set @YeBan=0
    end 
    
   end
   declare @owrt decimal(18,2),@owtotal decimal(18,2)
   select @owtotal=cast(sum(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))) as decimal(10,2))/60.0,
   @owrt=sum(RestTime)/60.0
    from ShiftDetail where ParentID=@ShiftID
   set @temp=0
 
   if(@onduty1 is not null and @offduty1 is not null)
   begin
    select @temp=@temp+datediff(minute,@onduty1,@offduty1)
   end
   if(@onduty2 is not null and @offduty2 is not null)
    select @temp=@temp+datediff(minute,@onduty2,@offduty2)
   if(@onduty3 is not null and @offduty3 is not null)
    select @temp=@temp+datediff(minute,@onduty3,@offduty3)
   set @temp=@temp*30/1800

   if @OWMul=2
   begin
    set @ow2=@temp-@owrt
    if(@ow2>@owtotal-@owrt)
     set @ow2=@owtotal-@owrt
    if(@ow2<0)
     set @ow2=0
   end
   if @OWMul=3
   begin
    set @ow3=@temp-@owrt
    
    if(@ow3>@owtotal-@owrt)
     set @ow3=@owtotal-@owrt
    if(@ow3<0)
     set @ow3=0
   end
  end
  
  ----------------------------------------------------
  --判断该员工是否出差,出差则已加班处理,并且没有申请加班
  
  if exists (select 1 from EmployeeLeave where StaffNo=@StaffNo and owflag=1 and datediff(day,Fdate,@date)=0 and Status=3 and HolidayType in ('003','014')) and @baoan<>1
  begin
   select @Hours = case WholeDay when 3 then 8 when 1 then 4 when 2 then 4 else Hours end from EmployeeLeave
   where StaffNo=@StaffNo and datediff(day,FDate,@date)=0

   if @OWMul=2
    set @OW2=@Hours*60

   if @OWMul=3
    set @OW3=@Hours*60

   if @OWMul=1.5
    set @OW1=@Hours*60
   
   if exists (select 1 from WorkDayRecord where StaffNo=@StaffNo and Fdate=@Date)
    update WorkDayRecord set ShouldTime=case @HolidayType when '001' then 8 else null end,RealTime=case @HolidayType when '001' then 8 else null end,
    OverWork1 = @OW1,OverWork2=@OW2,Overwork3=@OW3,Neglect=0,CC=@Hours/60,BJ=0,SJ=0,HJ=0,Saj=0,GSJ=0,NJ=0,CJ=0,PCJ=0,WX=0,BK=0,YeBan=0,TongXiao=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=0
    where StaffNo=@StaffNo and FDate=@date
   else
    insert into WorkDayRecord
    (StaffNo,FDate,Overwork1,Overwork2,Overwork3,CC,RealTime,ShouldTime)
    values (@StaffNo,@date,@OW1,@OW2,@OW3,case when @Hours>8 then 8 else @hours end,case @HolidayType when '001' then 8 else null end,case @HolidayType when '001' then 8 else null end)
   
  end
  
  if(@baoan=1)
  begin
    select @BK = count(FDate) from WorkTimeAdd where StaffNo=@StaffNo and DateDiff(Day,FDate,@Date)=0 and FType='001'

    if exists (select 1 from WorkDayRecord where FDate=@Date and StaffNo=@StaffNo)
     update WorkDayRecord set OnDuty1=@OnDuty1,OffDuty1=@OffDuty1,OnDuty2=@OnDuty2,OffDuty2=@OffDuty2,OnDuty3=@OnDuty3,OffDuty3=@OffDuty3,
     OverWork1=@OW1,OverWork2=@OW2,OverWork3=@OW3,
     Neglect=@NegLect,LateEarly=@LateEarly,shiftID=@ShiftID,
     YeBan=@YeBan,TongXiao=@TongXiao,BK=@BK,LateNum=@LateNum,WX=0,ShouldTime=null,RealTime=null,CC=0,BJ=0,SJ=0,HJ=0,SaJ=0,GSJ=0,NJ=0,CJ=0,PCJ=0,AppOWHours=@OWApply,OWAbnormFlag=0
     where FDate=@Date and StaffNo=@StaffNo
    else
     insert into WorkDayRecord (shiftid,StaffNo,FDate,OnDuty1,OffDuty1,OnDuty2,OffDuty2,OnDuty3,OffDuty3,OverWork1,OverWork2,OverWork3,NegLect,LateEarly,YeBan,TongXiao,BK,LateNum,AppOWHours,OWAbnormFlag)
     Values (@shiftid,@StaffNo,@date,@OnDuty1,@OffDuty1,@OnDuty2,@OffDuty2,@OnDuty3,@OffDuty3,@OW1,@OW2,@OW3,@Neglect,@LateEarly,@YeBan,@TongXiao,@BK,@LateNum,@OWApply,0)
         if @HolidayType='001'

     update WorkDayRecord set ShiftID=@defaultshift,ShouldTime=8,RealTime=8 where staffno=@staffNo and FDate=@date
  end
  else
  begin
   
   --判断当天该员工有没有申请加班，如果没有，则该日不用上班删掉当天的数据
   if not exists (select 1 from OverWork where (datediff(day,StartTime,@Date)=0 or abs(datediff(hour,convert(varchar(10),@date,120) + ' 23:59:59',StartTime)) between 0 and 4 ) and StaffNo=@StaffNo and Status=3 and FType in ('001','003') and StartTime>isNull(@MaxDate,@date)) and not exists (select 1 from EmployeeLeave where StaffNo=@StaffNo and datediff(day,Fdate,@date)=0 and Status=3 and HolidayType in ('003','014'))
   begin
    
    if (select Count(staffno) from WorkTime where staffno=@staffno and datediff(day,fdate,@date)=0 and fdate>isNull(@maxdate,@date))  >=2
    begin
     if exists (select 1 from workDayRecord where staffno=@staffno and FDate=@date)
      update WorkDayRecord set ShiftID=null,ShouldTime=null,RealTime=null,OnDuty1=null,OffDuty1=null,OnDuty2=null,OffDuty2=null,OnDuty3=null,OffDuty3=null,LateEarly=null,Neglect=null,OverWork1=null,OverWork2=null,OverWork3=null,
      CC=0,BJ=0,SJ=0,HJ=0,SaJ=0,GSJ=0,NJ=0,CJ=0,PCJ=0,WX=0,BK=0,BRJ=0,YeBan=0,TongXiao=0,AdjustRest=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=0,OWAbnormFlag=1
      where staffno=@staffno and fdate=@date
     else
      insert into workDayRecord (staffno,fdate,OWAbnormFlag) values (@staffno,@date,1)
    end
    else
     delete from WorkDayRecord where StaffNo=@StaffNo and Datediff(day,FDate,@Date)=0

    if @HolidayType='001'
    begin
     if @DutyDate>@date or @date >getdate() or (@DimissionDate is not null and @DimissionDate<=@date)
      select @RealTime=0,@WX = 8
     else
      select @RealTime = 8,@WX=0

     if exists (select 1 from workDayRecord where staffno=@staffno and FDate=@date)
      update workDayRecord
      set ShiftID=@defaultshift,ShouldTime=8,
      RealTime=@RealTime,
      WX=@WX,
      OnDuty1=null,OffDuty1=null,OnDuty2=null,OffDuty2=null,OnDuty3=null,OffDuty3=null,LateEarly=null,Neglect=null,OverWork1=null,OverWork2=null,OverWork3=null,
      CC=0,BJ=0,SJ=0,HJ=0,SaJ=0,GSJ=0,NJ=0,CJ=0,PCJ=0,BK=0,BRJ=0,YeBan=0,TongXiao=0,AdjustRest=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=0,OWAbnormFlag=0
      where staffno=@staffno and fdate=@date
     else
      insert into WorkDayRecord (StaffNo,FDate,ShiftID,ShouldTime,RealTime,WX) values (@StaffNo,@date,@defaultshift,8, @RealTime,@WX)
    end
    goto goOn
   end
   else--------计算加班
   begin
    
    
    select @Neglect = 0,@LateEarly = 0,@LateNum=0,@OWTime=0,@OShiftID = null,@OPeriodTime = 0
    declare @i int
    set @i=1
    --查找加班时间
    declare cur cursor for
    select StartTime,EndTime,Hours,FType,ShiftID,PeriodTime from overwork
    where startTime between @date and dateadd(hour,30,@date) and StaffNo=@StaffNo and Status=3 and FType in ('001','003') and StartTime>isNull(@maxDate,@date)
    
    order by StartTime

    open  cur
    fetch next from cur into @StartTime,@EndTime,@Hours,@OWType,@OShiftID,@OPeriodTime
    while @@FETCH_STATUS=0
    begin

     set @OWApply = @OWApply + @Hours
     
     set @OnDuty = null
     set @OffDuty = null
    
    
    select top 1 @OnDuty = 打卡时间
    from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=320
    
    and 打卡时间>isNull(@maxDate,@date)
    order by abs(datediff(minute,@StartTime,打卡时间)) asc
     
     if @OnDuty is not null
     begin
      
      select @OffDuty=min(打卡时间) from #t1 where 打卡时间>@OnDuty
      if abs(datediff(minute,@OffDuty,@EndTime))>300 set @OffDuty=null
      if @OffDuty is not null
      begin
       set @temp=datediff(minute,case when @OnDuty< @StartTime then @StartTime else @OnDuty end,@OffDuty)
       select @temp = case when @temp>@Hours then @Hours else @temp end

       if @OShiftID is not null and @OPeriodTime>0
        select @temp = @temp - RestTime from ShiftDetail where ParentID=@OShiftID and PeriodTime=@OPeriodTime
       if @temp < 0
        set @temp =0
       set @OWTime = @OWTime + @temp
      end
     end
     if(@OnDuty is not null and @OffDuty is not null)
      set @maxDate=isNull(@OffDuty,@OnDuty)
     --else
      --set @maxDate=@endTime
  
     if @OWType='003' --如果加班类别为"必须",则计算迟到及旷工
     begin
      if @OnDuty is Null or @OffDuty is Null
       set @NegLect = @NegLect + @Hours
      else
      begin
       set @temp=datediff(minute,@StartTime,@OnDuty)
       if @temp>0
       begin
        if @temp>30
         set @NegLect=@NegLect + @temp/60
         
        else
         select @LateEarly=@LateEarly+@temp, @LateNum=@LateNum+1
       end
      end

     end
     if @i=1
      select @OnDuty1 = @OnDuty,@OffDuty1 = @OffDuty
     if @i=2
      select @OnDuty2 = @OnDuty,@OffDuty2 = @OffDuty
     if @i=3
      select @OnDuty3 = @OnDuty,@OffDuty3 = @OffDuty

     set @i=@i+1
     fetch next from cur into @StartTime,@EndTime,@Hours,@OWType,@OShiftID,@OPeriodTime
    end
    close cur
    deallocate cur
    set @temp = @OwTime
    set @temp = @temp /30 * 30
    set @OWTime = @temp
    
    if @OWMul=1.5
     set @OW1=isNull(@OW1,0) + @OWTime
    if @OWMul=2
     set @OW2=isNull(@OW2,0) + @OWTime
    if @OWMul=3
     set @OW3=isNull(@OW3,0) + @OWTime
    --通宵及夜班 
/*
   select @onduty1 as fdate into #tx1
    union all
    select @OffDuty1
    union all
    select @onduty2
    union all
    select @OffDuty2
    union all
    select @onduty3
    union all
    select @OffDuty3
    select @txMin=min(fdate),@txMax=max(fdate) from #tx1 where fdate is not null
    drop table #tx1
*/
   if(@offduty3 is not null)
   begin
    set @txMax=@offduty3
    set @txMin=@Onduty1
   end
   else if(@offduty2 is not null)
   begin
    set @txMax=@offduty2
    set @txMin=@Onduty1
   end
   else
   begin
    set @txmax=@offduty1
    set @txMin=@Onduty1
   end
   if(left(@orgcode,3)<>'002')
   begin
    if @txMax>=dateadd(day,1,@date)+' 02:00:00'
     set @TongXiao=1
    else
     set @TongXiao=0
    if(@TongXiao=0) and left(@orgcode,0)<>'002'
    begin
     if @txMax>=dateadd(day,1,@date)+' 00:00:00'
     set @YeBan=1
    else
     set @YeBan=0
    end
    
    
   end
   else
   begin
    if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 06:30:00')
     set @TongXiao=1
    else
     set @TongXiao=0
    if(@TongXiao=0)
    begin
     if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 00:00:00')
      set @YeBan=1
     else
      set @YeBan=0
    end 
    
   end
    set @OW1 = @OW1/60 --cast(cast(@OW1 as int)/15*15  as decimal(18,2)) /60
    set @OW2 = @OW2/60 --cast(cast(@OW2 as int)/15*15  as decimal(18,2)) /60
    set @OW3 = @OW3/60 --cast(cast(@OW3 as int)/15*15  as decimal(18,2)) /60
    set @NegLect = @NegLect/60
    
    select @BK = count(FDate) from WorkTimeAdd where StaffNo=@StaffNo and DateDiff(Day,FDate,@Date)=0 and FType='001'

    if exists (select 1 from WorkDayRecord where FDate=@Date and StaffNo=@StaffNo)
     update WorkDayRecord set OnDuty1=@OnDuty1,OffDuty1=@OffDuty1,OnDuty2=@OnDuty2,OffDuty2=@OffDuty2,OnDuty3=@OnDuty3,OffDuty3=@OffDuty3,
     OverWork1=@OW1,OverWork2=@OW2,OverWork3=@OW3,
     Neglect=@NegLect,LateEarly=@LateEarly,shiftID=@ShiftID,
     YeBan=@YeBan,TongXiao=@TongXiao,BK=@BK,LateNum=@LateNum,WX=0,ShouldTime=null,RealTime=null,CC=0,BJ=0,SJ=0,HJ=0,SaJ=0,GSJ=0,NJ=0,CJ=0,PCJ=0,AppOWHours=@OWApply,OWAbnormFlag=0
     where FDate=@Date and StaffNo=@StaffNo
    else
     insert into WorkDayRecord (shiftid,StaffNo,FDate,OnDuty1,OffDuty1,OnDuty2,OffDuty2,OnDuty3,OffDuty3,OverWork1,OverWork2,OverWork3,NegLect,LateEarly,YeBan,TongXiao,BK,LateNum,AppOWHours,OWAbnormFlag)
     Values (@shiftid,@StaffNo,@date,@OnDuty1,@OffDuty1,@OnDuty2,@OffDuty2,@OnDuty3,@OffDuty3,@OW1,@OW2,@OW3,@Neglect,@LateEarly,@YeBan,@TongXiao,@BK,@LateNum,@OWApply,0)
    
    if @HolidayType='001'

     update WorkDayRecord set ShiftID=@defaultshift,ShouldTime=8,RealTime=8 where staffno=@staffNo and FDate=@date
   end
  end
 end
 else
 begin --不放假
  ---------------------------------1210修改
  
  select @OnDuty=null,@OffDuty=null
  select @OnDuty=a.Fdate,@OffDuty=b.Fdate from employeeLeave a left join WorkRepair b on a.LeaveID=b.LeaveID
  where Staffno=@Staffno and a.FDate=@date and WholeDay=3 and HolidayType='011' and a.status=3
  
  if @OnDuty is not null--调休一天
  begin
   
   delete from workDayRecord where staffno=@staffno and fdate=@date
   if @OffDuty is  null--补班
   begin

    select @LeaveHours=480,@LeaveDayType='011',@LeaveDayPart=3,@ShouldTime=8
    insert into workDayRecord (staffno,shiftid,fdate,ShouldTime,AdjustRest) values (@staffno,@defaultshift,@date,@ShouldTime,480)
    
    goto OverWorkCal
   end
   else
    goto goOn
  end
  
  if exists (select 1 from EmployeeLeave a left join WorkRepair b on a.LeaveID=b.LeaveID where a.Staffno=@Staffno and a.HolidayType='011' and a.WholeDay=3 and b.FDate=@date and datediff(month,'2008-6-1',a.fdate)>=0) and @OWMul>1.5
   set @OWMul=1.5
  --如果入职日期大于统计日期或统计日期大于当前日期则以无薪处理
  if @date<@DutyDate or @date>getdate()
  begin
   
   if exists (select 1 from WorkDayRecord where StaffNo=@StaffNo and FDate=@Date)
    update WorkDayRecord set ShiftID=@defaultshift,ShouldTime=8,RealTime=0,LateEarly=0,Neglect=0,OverWork1=0,OverWork2=0,OverWork3=0,CC=0,BJ=0,SJ=0,HJ=0,Saj=0,GSJ=0,NJ=0,CJ=0,PCJ=0,WX=8,BK=0,YeBan=0,TongXiao=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=@OWApply
    where Staffno=@StaffNo and FDate=@Date
   else
    insert into WorkDayRecord (StaffNo,FDate,ShiftID,ShouldTime,WX)
    values (@StaffNo,@Date,@defaultshift,8,8)
   goto goOn
  end

 

  --如果离职日期小于等于统计日期则以无薪处理
  if @DimissionDate<=@date
  begin

   if exists (select 1 from WorkDayRecord where StaffNo=@StaffNo and FDate=@Date)
    update WorkDayRecord set ShiftID=@defaultshift,ShouldTime=8,RealTime=0,LateEarly=0,Neglect=0,OverWork1=0,OverWork2=0,OverWork3=0,CC=0,BJ=0,SJ=0,HJ=0,Saj=0,GSJ=0,NJ=0,CJ=0,PCJ=0,WX=8,BK=0,YeBan=0,TongXiao=0,YGFJ=0,UpdateFlag=0,LateNum=0,AppOWHours=@OWApply
    where Staffno=@StaffNo and FDate=@Date
   else
    insert into WorkDayRecord (StaffNo,FDate,ShiftID,ShouldTime,WX)
    values (@StaffNo,@Date,@defaultshift,8,8)
   goto goOn
  end
  
  --获取当天请假数据
  select top 1 @LeaveDayPart=isNull(WholeDay,0),@LeaveDayType=HolidayType,@LeaveHours=case WholeDay when 3 then 480 when 2 then 240 when 1 then 240 else Hours * 60 end from EmployeeLeave where StaffNo=@Staffno and FDate=@date and Status=3 and WholeDay not in (1,2) order by WholeDay desc
  
  
  --判断如果请假一整天
  if @LeaveDayPart=3
  begin
  
   select @LeaveHours=480,@ShiftID=@defaultshift,@OnDuty=null,@OffDuty=null,
   --@OnDuty1=null,@OffDuty1=null,@OnDuty2=null,@OffDuty2=null,@OnDuty3=null,@OffDuty3=null,
   @OWTime=0,@Hours=0,@ShouldTime=8
   if @LeaveDayType in ('003','014')
    set @RealTime=8
   goto OverWorkCal
  end
  --判断是否更新了班次
  select @ShiftID=ShiftID from WorkDayRecord where FDate=@Date and StaffNo=@StaffNo and UpdateFlag=1

  create table #t2(
  ShiftID nvarchar(10),PeriodTime smallint,AheadTime int,AbsenceTimeA int,上班时间 datetime,
  AbsenceTimeL int,下班时间 datetime,DelayTime int,OWTime int,AdjustRest int,WX int)
  
  --班次信息,如果更新过班次,则以设置的班次为准
  
  if isNull(@ShiftID,'') <> ''
  begin
   insert into #t2
   select
   ParentID,
   PeriodTime,
   AheadTime,
   datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeA,
   --AbsenceTimeA,
   dateadd(day,OnDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OnDutyTime as smalldatetime)) as 上班时间,
   datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeL,
   --AbsenceTimeL,
   dateadd(day,OffDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OffDutyTime as smalldatetime)) as 下班时间,
   DelayTime,OWTime,AdjustRest,WX
   from ShiftDetail where ParentID=@ShiftID
  end
  else
  begin --获取该员工所有班次
   
   
   insert into #t2
   select
   ShiftID,
   PeriodTime,
   AheadTime,
   datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeA,
   --AbsenceTimeA,
   dateadd(day,OnDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OnDutyTime as smalldatetime)) as 上班时间,
   datediff(minute,dateadd(day,ONDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime) ) -30 as AbsenceTimeL,
   --AbsenceTimeL,
   dateadd(day,OffDutyDay,cast(convert(varchar(10),@date,120) + ' ' + OffDutyTime as smalldatetime)) as 下班时间,
   DelayTime,OWTime,AdjustRest,WX
   from EmployeeShift a
   inner join ShiftDetail b on a.ShiftID=b.ParentID
   where a.EmployeeID=@EmployeeID
  end
  
  select
  a.打卡时间,
  b.ShiftID as 班次编号,
  b.PeriodTime as 时间段,
  isNull(b.OWTime,0) as 加班,
  isNull(b.AdjustRest,0) as 调休,
  isNull(b.WX,0) as 无薪,
  cast(datediff(second,打卡时间,b.上班时间) as decimal(10,2))/60 as 上班时间差,
  cast(datediff(second,b.下班时间,打卡时间) as decimal(10,2))/60 as 下班时间差
  into #t3
  from #t1 a ,#t2 b
  where
  cast(datediff(second,a.打卡时间,b.上班时间) as decimal(10,2))/60 between AbsenceTimeA*-1 and AheadTime
  or cast(datediff(second,b.下班时间,a.打卡时间) as decimal(10,2))/60 between AbsenceTimeL*-1 and DelayTime
  

  select top 1 @shiftid=班次编号
   from(
   select 班次编号,时间段,min(abs(case when 上班时间差<0 then 上班时间差-50 else 上班时间差 end)) as 上班时间差,min(abs(case when 下班时间差<0 then 下班时间差-50 else 下班时间差 end)) as 下班时间差
   from #t3
   group by 班次编号,时间段)a group by 班次编号
   order by sum(上班时间差+下班时间差)
  /*
  select top 1 @ShiftID=班次编号 from #t3
  group by 班次编号
  order by count(班次编号) desc,sum(abs(上班时间差)),min(打卡时间)
  */
 
  --没有找到匹配的班次,则取默认班次
  IF @ShiftID is null
  begin
   select top 1 @ShiftID=ShiftID from EmployeeShift where EmployeeID=@EmployeeID
   if @ShiftID is null
    set @ShiftID=@defaultshift
  end
  
  --置空
  select @OnDuty=null,@OffDuty=null,@OnDuty1=null,@OffDuty1=null,@OnDuty2=null,@OffDuty2=null,@OnDuty3=null,@OffDuty3=null,
  @ShouldTime=0, @OWTime=0,@StartTime=null
  
  

Answer 2

  --上班前加班 20090310修改，上班前多次加班 
  select top 1 @StartTime=StartTime,@EndTime=EndTime,@Hours=Hours,@OWType=FType from overwork
  where datediff(day,StartTime,@Date)=0 and StartTime>dateadd(hour,7,@date)
  and StartTime<(select 上班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=1)
  and StartTime>isNull(@MaxDate,@date)
  and FType in ('001','003')
  and StaffNo=@StaffNo and Status=3
  and @RepairFlag=0
  
  if @StartTime is Not Null
  begin
   set @OWApply = @OWApply + @Hours
   select @OnDuty = min(打卡时间)
   from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=120
   and 打卡时间>isNull(@maxDate,@date)
   
   if @OnDuty is not null
   begin
    
    select @OffDuty=min(打卡时间) from #t1 where 打卡时间>@OnDuty and abs(datediff(minute,@EndTime,打卡时间))<=180
    
    if @OffDuty is not null
    begin
     set @temp=datediff(minute,@OnDuty,@OffDuty)
     if @temp > @Hours
      set @temp=@Hours

     set @temp = @temp /30 * 30
     if @OWMul=1.5
      set @OW1=@OW1 + @temp
     if @OWMul=2
      set @OW2=@OW2 + @temp
     if @OWMul=3
      set @OW3=@OW3 + @temp
    end
   end
   if @OWType='003' --必须计算迟到及旷工
   begin
    if @OnDuty is Null or @OffDuty is Null
     set @NegLect = @NegLect + @Hours
    else
    begin
     set @temp=datediff(minute,@StartTime,@OnDuty)
     if @temp>0
     begin
      if @temp>30
       set @NegLect=@NegLect + @temp
      else
       select @LateEarly=@LateEarly+@temp,@LateNum=@LateNum+1
     end
    end
   
   end
  end
  
  
  if @OnDuty is not null and @OffDuty is not null
   set @maxDate=@OffDuty

  
 
  --取上下班时间
  if @shiftID is not null
  begin
   select PeriodTime,cast(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))-RestTime as decimal(10,2))/60 as 应勤,
   AdjustRest as 调休,WX as 无薪,OWTime as 加班 into #t4
   from ShiftDetail where ParentID=@ShiftID

   -------班次的时间段
   declare @PeriodCount smallint,@shiftadd bit
   set @shiftadd=0
   select @i=0,@PeriodCount=count(ParentID) from ShiftDetail where ParentID=@ShiftID
   if @PeriodCount=1 and exists(select 1 from employeeleave where staffno=@staffno and wholeday=0 and fdate=@date)
   begin
    
    declare @ons datetime,@offs datetime,@shiftcc int
    select @ons=convert(char(11),dateadd(day,ondutyday,@date),120)+OnDutyTime+':00',@offs=convert(char(11),dateadd(day,offdutyday,@date),120)+OffDutyTime+':00'
     from shiftdetail where parentid=@shiftid
    select @shiftcc=count(1) from #t1
     where 打卡时间 between @ons and @offs
    if(@shiftcc>=2)
     select @PeriodCount=@PeriodCount+1,@shiftadd=1
   end
   while @i<@PeriodCount
   begin
    select @i=@i+1,@OnDutyBal=0,@OffDutyBal=0,@OnDuty=null,@OffDuty=null,@OWTime=0,@WXS=0,@AdjustRestS=0
    
    if exists (select 1 from #t2 where ShiftID=@ShiftID and PeriodTime=1 and 上班时间>getdate())
    begin
     
     select @WX = @WX + 应勤 from #t4 where PeriodTime=@i
     continue
    end
    --请假 上午和下午
    
    if exists (select 1 from EmployeeLeave where staffno=@staffno and fdate=@date and Status=3 and WholeDay=@i)
    begin
     Declare @lType varchar(3),@lHours int
     select @LeaveDayPart = @i,@lType = HolidayType from EmployeeLeave where staffno=@staffno and FDate=@date and status=3 and WholeDay=@i
     if @shiftadd=1
     select @lHours = datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))- RestTime
     from ShiftDetail where PeriodTime=1 and ParentID=@ShiftID
     else
     select @lHours = datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime))- RestTime
     from ShiftDetail where PeriodTime=@i and ParentID=@ShiftID
     if @lType='001' --事假
     begin
      select @SJ = @SJ + @lHours/60.0
      continue
     end
  
     if @lType='002' --病假
     begin
      set @BJ = @BJ + @lHours/60.0
      continue
     end
  
     if @lType in ('003','014')--出差/外勤
     begin
      select @CC = @CC + @lHours/60.0,@RealTime = @RealTime + @lHours/60.0

      continue
     end
  
     if @lType='004' --年假
     begin
      set @NJ = @NJ + @lHours /60.0
      continue
     end
  
     if @lType='005' --婚假
     begin
      set @HJ = @NJ + @lHours/60.0
      continue
     end
  
     if @lType='006' --产假
     begin
      set @CJ = @CJ + @lHours/60.0
      continue
     end
  
     if @lType='007' --陪产假
     begin
      set @PCJ = @PCJ + @lHours/60.0
      continue
     end
  
     if @lType='008' --工伤假
     begin
      set @GSJ = @GSJ + @lHours/60.0
      continue
     end
  
     if @lType='009' --丧假
     begin
      set @SaJ = @Saj + @lHours/60.0
      continue
     end
  
     if @lType='010' --因公放假
     begin
      set @YGFJ = @YGFJ + @lHours/60.0
      continue
     end
     
     if @lType = '011' --调休
     begin
      set @AdjustRest = @AdjustRest + @lHours
      continue
     end
  
     if @lType = '012' --无薪
     begin
      set @WX = @WX + @lHours/60.0
      continue
     end
    end
    declare @lhTotal decimal(18,2)
    select @lhTotal=sum(hours) from employeeleave where staffno=@staffno and FDate=@date and status=3 and WholeDay=0
    if(@lhTotal>=8)
     continue
    
    --取上班时间
    if @shiftadd=1
     select top 1 @OnDuty=打卡时间,@OnDutyBal=上班时间差,@temp=加班,@AdjustRestS=调休,@WXS = 无薪 from #t3 a
     inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
     where b.ShiftID=@shiftID and a.时间段=1 and 上班时间差 between b.absenceTimeA*-1 and b.AheadTime  and 打卡时间>isNull(@maxDate,@date)
     order by 上班时间差 desc--abs(上班时间差)
    else
     select top 1 @OnDuty=打卡时间,@OnDutyBal=上班时间差,@temp=加班,@AdjustRestS=调休,@WXS = 无薪 from #t3 a
     inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
     where b.ShiftID=@shiftID and a.时间段=@i and 上班时间差 between b.absenceTimeA*-1 and b.AheadTime  and 打卡时间>isNull(@maxDate,@date)
     order by 上班时间差 desc--abs(上班时间差)

    if exists (select 1 from #t2 where ShiftID=@ShiftID and PeriodTime=@i and 下班时间>getdate())
    begin
     select @WX = @WX + 应勤 from #t4 where PeriodTime=@i
     continue
    end
    
    --取下班时间
    
    if (@OnDuty is not null)
    begin
     if @shiftadd=1
      select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #t3 a
      inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
      and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) and 打卡时间>@OnDuty
      where b.ShiftID=@shiftID and a.时间段=1
      order by 下班时间差
     else
     begin
      
      select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #t3 a
      inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
      and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) and 打卡时间>@OnDuty
      where b.ShiftID=@shiftID and a.时间段=@i
      order by 下班时间差
      
     end
    end
    else

     begin
     if (select count(distinct 打卡时间) from #t3 where 班次编号=@SHiftID and 打卡时间<=(select min(打卡时间) from #t3 where 班次编号=@ShiftID and 打卡时间>(select 下班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=@i+1)))=2 and @PeriodCount>=2 and @i=1
      goto g1
     if isNull(@LeaveHours,0)<(select datediff(minute,上班时间,下班时间) from #t2 where ShiftID=@ShiftID and PeriodTime=@i)
     begin
      
      select top 1 @OffDuty=打卡时间,@OffDutyBal=下班时间差 from #t3 a
      inner join #t2 b on a.班次编号= b.ShiftID and a.时间段=b.PeriodTime
      and (下班时间差 between b.absenceTimeL*-1 and b.DelayTime) 
      where b.ShiftID=@shiftID and a.时间段=@i
      order by 下班时间差
      if exists(select 1 from overwork where staffno=@staffno and status=3 and abs(datediff(minute,@OffDuty,starttime))<30)
       set @OffDuty=null
     end
     end
    g1:
    set @AdjustRest = @AdjustRest + @AdjustRestS/60
    set @WX = @WX + @WXS/60
    
    if @OnDuty is not null and @OffDuty is not null
    begin
     
     if @temp>=isNUll(@LeaveHours,0)
     set @OWTime = @OWTime + @temp-@LeaveHours
     if(@shiftadd<>1)
     begin
      select
      @LateEarly=@LateEarly + case when @OnDutyBal<0 then ceiling(abs(@OnDutyBal)) else 0 end + case when @OffDutyBal<0 then ceiling(abs(@OffDutyBal)) else 0 end
      
      if(@LateEarly>0 and @HolidayType<>'004')
       set @LateNum=@LateNum+1
     end
   
    if @i=1
     select @OnDuty1=@OnDuty,@OffDuty1=@OffDuty
    if @i=2
     select @OnDuty2=@OnDuty,@OffDuty2=@OffDuty
    if @i=3
     select @OnDuty3=@OnDuty,@OffDuty3=@OffDuty
    
    declare @onTemp datetime,@offTemp datetime
    
 
   select @onTemp=convert(char(11),dateadd(day,ondutyday,@date),120)+Ondutytime+':00',@offTemp=convert(char(11),dateadd(day,offdutyday,@date),120)+offdutytime+':00' from shiftdetail where periodtime=@i and parentid=@shiftid
     if(@shiftadd=1)
     begin
     
      if(@onDuty<@onTemp)
        set @onduty=@onTemp
       if(@offDuty>@offtemp)
        set @offduty=@offtemp
       set @RealTime = @RealTime + datediff(minute,@OnDuty,@OffDuty)/60.0
      --select @onduty,@
     end
     else
      begin
      /*
      if datediff(minute,@OnDuty,@OffDuty)>(select 应勤*60 from #t4 where PeriodTime=@i)
       select @RealTime = @RealTime + 应勤 from #t4 where PeriodTime = @i
      else
      begin
       set @RealTime = @RealTime + datediff(minute,case when @OnDuty<@ontemp then @ontemp else @onduty end,case when @OffDuty>@offtemp then @offtemp else @offduty end)/60.0
      end
      */
      declare @realtemp decimal(18,2)
      set @realtemp=datediff(minute,case when @OnDuty<@ontemp then @ontemp else @onduty end,case when @OffDuty>@offtemp then @offtemp else @offduty end)
      if datediff(minute,@ontemp,@offtemp)-@realtemp<15
      begin
       set @RealTime = @RealTime + datediff(minute,@ontemp,@offtemp)/60.0
       --set @neglect = @neglect +
      end
      else
       
       set @RealTime = @RealTime + @realtemp/60.0
      
     end
     
    end
    else
    begin
 
     if isNull(@DayPart,'')<>cast(@i as varchar(1)) and isNull(@HolidayType,'')<>'001'
     select @Neglect = @Neglect + 应勤 *60  from #t4 where PeriodTime=@i
     
    end
  
    set @Hours = 0
    --前直落处理
    

    select @Hours=Hours,@StartTime=StartTime,@EndTime=EndTime from OverWork
    where StaffNo=@StaffNo and EndTime=(select 上班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=@i)
    and FType='004' and Status=3

    if @Hours>0
    begin
     set @OWApply = @OWApply + @Hours
     set @temp = isNull(datediff(minute ,@OnDuty,@EndTime),0)
     if @temp>@Hours
      set @temp=@Hours

     set @OWTime = @OWTime + @temp/30*30
     set @Hours=0
    end

    declare @OffDutyTime smalldatetime
    select @OffDutyTime = 下班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=@i
    --后直落处理
    
     
    select @Hours=Hours,@StartTime=StartTime,@EndTime=EndTime from OverWork
    where StaffNo=@StaffNo and StartTime=@OffDutyTime--(select 下班时间 from #t2 where ShiftID=@ShiftID and PeriodTime=@i)
    and FType='002' and Status=3
    if @Hours<=0 and @RepairFlag=1
    select @Hours = datediff(Minute,@OffDutyTime,EndTime),@StartTime=@OffDutyTime,@EndTime=EndTime from overwork
    where StaffNo=@Staffno and @OffDutyTime between StartTime and EndTime
    if @Hours>0
    begin
     set @OWApply = @OWApply + @Hours
     set @temp = isNull(datediff(minute ,@StartTime,@OffDuty),0)
     if @temp>@Hours
      set @temp=@Hours
     set @OWTime = @OWTime + @temp/30*30
    end
   
    
    if @DayPart=cast(@i as varchar(1)) and @OnDuty is not null and @OffDuty is not null
    begin
     set @temp = datediff(minute ,@OnDuty,@OffDuty)
     if(@shiftadd=1)
      select @temp=case when @temp>应勤 then 应勤*60 else @temp end from #t4 where periodTime=1
     else
      select @temp=case when @temp>应勤 then 应勤*60 else @temp end from #t4 where periodTime=@i
     set @OWTime= @temp/30*30
     if @OWMul=1.5
      set @OW1=@OW1+isNull(@OWTime,0)
     
     if @OWMul=2
      set @OW2=@OW2+isNull(@OWTime,0)

     if @OWMul=3
      set @OW3=@OW3+isNull(@OWTime,0)
    end
    else
    begin
     
     select @OW1 = @OW1 + isNull(@OWTime ,0)
     
    end

   
    if @OffDuty is not null and @OnDuty is not null
     set @maxDate=isNull(@OffDuty,@OnDuty)
    
    
   end
   
   if object_id('tempdb..#t4') is not null
   drop table #t4
  end

Answer 3

  --计算应勤
  declare @OWShift decimal(18,2),@owrest decimal(18,2),@owshiftFact  decimal(18,2)
  select @ShouldTime=cast(sum(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime)))-sum(RestTime) + sum(AdjustRest) + sum(WX)  as decimal(10,2))/60,
  @OWShift=sum(OWTime),@owshiftFact=sum(OWTime),@owrest=sum(resttime),@realtime=case when @shiftadd=1 then  @realtime-sum(RestTime)/60.0 else @realtime end
  from ShiftDetail where ParentID=@ShiftID
  if(@realtime<0)
   set @realtime=0
  
  
  
  if @DayPart is not null and @HolidayType not in ('001' ,'004')
  select @ShouldTime=@ShouldTime-
  cast(datediff(minute,dateadd(day,OnDutyDay,OnDutyTime),dateadd(day,OffDutyDay,OffDutyTime)) as decimal(10,2))/60
  from ShiftDetail where ParentID=@ShiftID and PeriodTime=cast(@DayPart as smallint)
  

  if(@lateearly<0)
   set @lateearly=0
  if(@realtime>@shouldtime)
   set @realtime=@shouldtime

  
OverWorkCal:
  
  if object_id('tempdb..#t2') is not null
   select @OnDuty = min(上班时间) ,@OffDuty=max(下班时间) from #t2 where ShiftID=@ShiftID
  else begin
   set @OffDuty=@date
  end
  
  declare cur cursor for
  
  select StartTime,EndTime,Hours,FType from overwork
  where (datediff(hour,@OffDuty,StartTime)<2 or
  --@OffDuty=StartTime or
  datediff(hour,convert(varchar(10),@date,120) + ' 23:59:59',StartTime)<=4
  )
  and StartTime>isNull(@maxDate,@date)
  and StaffNo=@StaffNo and Status=3 and (shiftid is null or ftype='002')
  order by StartTime

  open cur
  fetch next from cur into @StartTime,@EndTime,@Hours,@OWType
  
  set @OWTime=0
  while @@Fetch_status=0
  begin
   select @OWApply = @OWApply + @Hours,@OnDuty=null,@OffDuty=null
   
  if exists (select 1 打卡时间
   from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=320
   and 打卡时间<=@StartTime
   and 打卡时间>isNull(@maxDate,@date))
    
   select top 1 @OnDuty = 打卡时间
   from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=320
   
   and 打卡时间>isNull(@maxDate,@date)
   order by datediff(minute,@StartTime,打卡时间) asc
   
   else
    select top 1 @OnDuty = 打卡时间
     from #t1 where Abs(datediff(minute,@StartTime,打卡时间))<=320
   
     and 打卡时间>isNull(@maxDate,@date)
     order by Abs(datediff(minute,@StartTime,打卡时间)) asc
   
   declare @endTime1 smalldatetime
   select @endTime1=dateadd(minute,@hours,@StartTime)
   
   
    
   if @OnDuty is not null
   begin
    select @maxDate=@OnDuty
    select @OffDuty=min(打卡时间) from #t1 where 打卡时间>@OnDuty and abs(datediff(minute,@EndTime,打卡时间))<=300
    if @OffDuty is not null
    begin
     set @maxDate=@OffDuty
     --20090212修改,加班结束时间不对
     set @temp=datediff(minute,case when @OnDuty< @StartTime then @StartTime else @OnDuty end,case when @OffDuty<@endTime1 then @OffDuty else @endTime1 end)
     if @temp > @Hours
      set @temp=@Hours
     
     set @temp = @temp /30 * 30
     
     if @OWMul=2
      set @OW2=@OW2 + @temp
     else
     begin
      declare @OnDutytemp smalldatetime,@OffDutytemp smalldatetime
      if object_id('tempdb..#t2') is not null
       select @OnDutytemp=上班时间,@OffDutytemp=下班时间 from #t2 where ShiftID=@ShiftID and PeriodTIme=@DayPart
      if @OWMul=3 and (@dayPart='0' or (@StartTIme=@OnDutytemp and @EndTime=@OffDutytemp))
       set @OW3=@OW3 + @temp
      else
       set @OW1=@OW1 + @temp
     end
    end
   end
   if @OWType='003' --必须计算迟到及旷工
   begin
    if @OnDuty is Null or @OffDuty is Null
     set @NegLectOW = @NegLectOW + @Hours
    else

    begin
     set @temp=datediff(minute,@StartTime,@OnDuty)
     if @temp>0
     begin
      if @temp>30
       set @NegLectOW=@NegLectOW + @temp
      else
       select @LateEarly=@LateEarly+@temp ,@LateNum=@LateNum+1
     end
    end
   end
   if @OnDuty is not Null
    select @OnDuty3=@OnDuty
   if @OffDuty is not null
    select @OffDuty3=@OffDuty
   fetch next from cur into @StartTime,@EndTime,@Hours,@OWType
  end
  close cur
  deallocate cur

  if(@OffDuty3 is not null and @OnDuty3 is not null)
  set @maxDate=isNull(@OffDuty3,@OnDuty3)
  if(@offduty3 is not null)
   begin
    set @txMax=@offduty3
    set @txMin=@Onduty1
   end
   else if(@offduty2 is not null)
   begin
    set @txMax=@offduty2
    set @txMin=@Onduty1
   end
   else
   begin
    set @txmax=@offduty1
    set @txMin=@Onduty1
   end
  if(left(@orgcode,3)<>'002')
  begin
   if @txMax>=dateadd(day,1,@date)+' 02:00:00'
    set @TongXiao=1
   else
    set @TongXiao=0
   if(@TongXiao=0)
   begin
    if @txMax>=dateadd(day,1,@date)+' 00:00:00'
     set @YeBan=1
    else
     set @YeBan=0
   end
   
   
  end
  else
  begin
   
   if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 06:30:00')
    set @TongXiao=1
   else
    set @TongXiao=0
   if(@TongXiao=0)
   begin
    if(@txMin<@date+' 22:45:00' and @txMax>=dateadd(day,1,@date)+' 00:00:00')
     set @YeBan=1
    else
     set @YeBan=0
   end 
  end
  if @HolidayType='004' and @OWshift<=0
  begin
   
   set @WX = @WX+(@Neglect + @LateEarly)/60
   if @LeaveDayTYpe in ('003','014')
    set @WX = @WX - @LeaveHours/60
   if @WX < 0
    set @WX=0
   select @Neglect=0,@LateEarly=0
  end
  

   
  --如果迟到时间大于30分钟则为旷工
  /*
  if @LateEarly>=30
  begin
   set @Neglect = @Neglect + @LateEarly
   select @LateEarly=0, @LateNum  =0
  end
  */
  --如果旷工了并且已请假减去请假时间减
  
  --转换为小时
  
  if @OWShift>0 and @LeaveDayPart=0
  begin
   if @LeaveHours>@OWShift
    set @LeaveHours=@LeaveHours-@OWShift
   else
    select @LeaveHours=0
  end
  set @LeaveHours=@LeaveHours/60

  if exists(select 1 from employeeleave where staffno=@staffno and fdate=@date and wholeday=0 and status=3)
  begin
  select * into #employeeleave from employeeleave where staffno=@staffno and fdate=@date and wholeday=0 and status=3
  declare @ltotal decimal(18,2)
   select @ltotal=sum(hours) from employeeleave where staffno=@staffno and fdate=@date and wholeday=0 and status=3
  if(@ltotal is null)
   set @ltotal=0
  if(@realtime+@ltotal>@shouldtime)
   set @realtime=@shouldtime-@ltotal
 
  while exists(select 1 from #employeeleave)
  begin
      
   declare @leaveid int
   select top 1 @LeaveHours=hours,@leaveid=leaveid,@LeaveDayType=holidaytype from #employeeleave order by leaveid desc
   --如果请假，并且有旷工
  
   if (@lateearly>0 or @neglect>0) and @LeaveHours>0
   begin
    /*
    if @LeaveDayType in('003','013','014')
    begin
     set @realtime=@realtime+@LeaveHours
     if(@realtime>@shouldtime)
      set @realtime=@shouldtime
    end
    else
    */
     if(@realtime+@LeaveHours>@shouldtime)
      set @realtime=@shouldtime
    set @neglect=@neglect-@LeaveHours*60
    
    
    if(@neglect<0)
     set @neglect=0
    
   end

 
   if @lateearly>0 and @LeaveHours>0
   begin
    set @lateearly=@lateearly-@LeaveHours*60.0
    if(@lateearly<0)
     set @lateearly=0
   end
   if @LeaveDayType='001' --事假
   begin
    set @SJ = @SJ+@LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@SJ
   end

   if @LeaveDayType='002' --病假

   begin
    set @BJ = @BJ+@LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@BJ
   end

   if @LeaveDayType='003' or @LeaveDayType='014'--出差
   begin
    set @CC = @LeaveHours
    if @LeaveDayPart<>3
     set @RealTime = @RealTime + @LeaveHours
    if(@realtime>@shouldtime)
     set @realtime=@shouldtime
   end

   if @LeaveDayType='004' --年假
   begin
    set @NJ = @NJ+@LeaveHours
    
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@NJ
   end

   if @LeaveDayType='005' --婚假
   begin
    set @HJ = @HJ+@LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@HJ
   end

   if @LeaveDayType='006' --产假
   begin
    set @CJ = @CJ+@LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@CJ
   end

   if @LeaveDayType='007' --陪产假
   begin
    set @PCJ = @PCJ+@LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@PCJ
   end

   if @LeaveDayType='008' --工伤假
   begin
    set @GSJ = @GSJ+@LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@GSJ
   end

   if @LeaveDayType='009' --丧假
   begin
    set @SaJ = @SaJ+@LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@SaJ
   end

   if @LeaveDayType='010' --因公放假
   begin
    
    set @YGFJ = @YGFJ+@LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@YGFJ
   end
   
   if @LeaveDayType = '011' --调休
   begin
    set @AdjustRest = @AdjustRest+@LeaveHours * 60
    
    if(@LeaveHours+@RealTime>@shouldtime)
     set @RealTime=@shouldtime-@LeaveHours
   end

   if @LeaveDayType = '012' --无薪
   begin
    set @WX = @WX + @LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@WX
   end
  
   if @LeaveDayType = '013' --哺乳假
   begin
    set @BRJ = @BRJ+@LeaveHours
    if @LeaveDayPart<>3
    begin
     if @Neglect between 0.1 and 29
     begin
      set @LateEarly=@Neglect
      set @Neglect=0
     end
     
     set @RealTime = @RealTime + @LeaveHours
      if(@realtime>8)
       set @realtime=8
    
    end
   end
   /*
   if(@leavehours>0 and @neglect>0)
     begin
      --set @neglect=@neglect-@leavehours*60
      set @leavehours=0
      if(@neglect<0)
       set @neglect=0
     end
   */
   delete from #employeeleave where leaveid=@leaveid
   
  end
  
  drop table #employeeleave
  end
  else
  begin

   if @Neglect>0 and @LeaveHours>0
   begin
    set @neglect=@neglect-@LeaveHours
    if(@neglect<0)
     set @neglect=0
   end
   if @LeaveDayType='001' --事假
   begin
    set @SJ = @LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@SJ
    goto goUpdate
   end

   if @LeaveDayType='002' --病假

   begin
    set @BJ = @LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@BJ
    goto goUpdate
   end

   if @LeaveDayType='003' or @LeaveDayType='014'--出差
   begin
    set @CC = @LeaveHours
    
    if @LeaveDayPart<>3
     set @RealTime = @RealTime + @LeaveHours

    if(@realtime>@shouldtime)
     set @realtime=@shouldtime
    goto goUpdate

   end

   if @LeaveDayType='004' --年假
   begin
    set @NJ = @LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@NJ
    goto goUpdate

   end

   if @LeaveDayType='005' --婚假
   begin
    set @HJ = @LeaveHours
    goto goUpdate
   end

   if @LeaveDayType='006' --产假
   begin
    set @CJ = @LeaveHours
    goto goUpdate
   end

   if @LeaveDayType='007' --陪产假
   begin
    set @PCJ = @LeaveHours
    goto goUpdate
   end

   if @LeaveDayType='008' --工伤假
   begin
    set @GSJ = @LeaveHours
    goto goUpdate
   end

   if @LeaveDayType='009' --丧假
   begin
    set @SaJ = @LeaveHours
    goto goUpdate
   end

   if @LeaveDayType='010' --因公放假
   begin
    set @YGFJ = @LeaveHours
    goto goUpdate
   end
   
   if @LeaveDayType = '011' --调休
   begin
    set @AdjustRest = @LeaveHours * 60
    goto goUpdate
   end

   if @LeaveDayType = '012' --无薪
   begin
    set @WX = @WX + @LeaveHours
    if(@LeaveHours+@RealTime>8)
     set @RealTime=8-@WX
    goto goUpdate
   end

   if @LeaveDayType = '013' --哺乳假
   begin
    set @BRJ = @LeaveHours
    if @LeaveDayPart<>3
    begin
     if @Neglect between 1 and 29
     begin
      set @LateEarly=@Neglect
      set @Neglect=0
     end

     set @RealTime = @RealTime + @LeaveHours
    end
    goto goUpdate
   end
  end
  if(@lateearly>0 and @owshift>0)
  begin
   if (@lateearly>@owshift)
    select @lateearly=@lateearly-@owshift,@owshift=0
   else
    select @owshift=@owshift-@lateearly,@lateearly=0,@ow1=@owshift-@lateearly
   
  end
  
 
  --迟到的加上实勤
 
  goUpdate:
  select @BK = count(FDate) from WorkTimeAdd where StaffNo=@StaffNo and DateDiff(Day,FDate,@Date)=0 and FType='001'

  if @LeaveDayPart=0
  begin
   
   if @HolidayType='004' and @LeaveDayType='013'
    set @WX = @WX - @LeaveHours
   if @LeaveDayType='004'
    set @WX = @WX - @LeaveHours
   if @WX <0
    set @WX=0
  
   --set @RealTime=@ShouldTime  - @Neglect/60 -@WX
   
   if @LeaveDayType not in ('003','012' ,'013','014','011','010')
   begin
    if(@realtime+@leavehours>@shouldtime)
    set @RealTime = @shouldtime - isNull(@LeaveHours,0)
   end

Answer 4

   --迟到早退并且调休了就用调休减去迟到早退时间
   if @LateEarly>0 and @LeaveHours>0--@AdjustRest>0
   begin
    set @LateEarly=@LateEarly-@LeaveHours*60
    if @LateEarly<0 set @LateEarly=0
    set @LateNum=0
   end

  
  end
  if @LateEarly>=30
  begin
   set @Neglect = @Neglect + @LateEarly
   select @LateEarly=0, @LateNum  =0
  end
  if(@lateearly between 1 and 29)
  begin
   set @realtime=@realtime+@lateearly/60.0
   if(@realtime>@shouldtime)
    set @realtime=@shouldtime
  end
  
  if(@neglect between 0.1 and 29)
  begin
   set @Lateearly=@neglect
   set @realtime=@realtime+@neglect/60.0
   set @neglect=0
   if(@realtime>@shouldtime)
    set @realtime=@shouldtime
  end
  select @Neglect = @Neglect/60,@NegLectOW=@NegLectOW/60.0
  --

  if @LeaveDayPart<>3 and @owshiftFact>0
  begin
   
   select @ShouldTime=@ShouldTime-@owshiftFact/60.0
   
   
   if @RealTime>@ShouldTime
   begin
    select @RealTime=@ShouldTime---@neglect--,@OW1=@OW1-@Neglect*60
    --set @Neglect=0
   end
   if(@neglect>0)
   begin
    if(@OWshift/60>@neglect)
    begin
     select @OW1=@OW1-CEILING(2*@neglect)/2*60
     set @neglect=0
    end
    else
     set @OW1=0
   end
   
   if   @LeaveHours>0
   begin
    set @RealTime=8-@LeaveHours
    
    set @ow1=@owshift-@LeaveHours*60
    set @OWShift=0
   end
   if @realtime<@shouldtime
    set @ow1=0
   if @LateEarly>0
   begin
    set @OW1 = @OW1 - @LateEarly
    select @LateEarly=0, @LateNum=0
   end  
   if(@realtime+@neglect>@shouldtime)
    set @neglect=@shouldtime-@realtime  
  end 

  set @OW1=cast(@OW1/30 as int) *30
  if @OW1<0 set @OW1=0
  select @OW1=@OW1 / 60, @OW2=@OW2 / 60, @OW3=@OW3 / 60,@Neglect=@Neglect+@NeglectOW
 
  if exists(select 1 from WorkDayRecord where StaffNo=@StaffNo and Datediff(day,FDate,@Date)=0)
   update WorkDayRecord set ShiftID=@ShiftID,OnDuty1=@OnDuty1,OffDuty1=@OffDuty1,
   OnDuty2=@OnDuty2,OffDuty2=@OffDuty2,
   OnDuty3=@OnDuty3,OffDuty3=@OffDuty3,
   OverWork1=@OW1,OverWork2=@OW2,OverWork3=@OW3,
   ShouldTime=@ShouldTime,
   RealTime=@Realtime,
   LateEarly=@LateEarly,
   LateNum=@LateNum,
   Neglect=@Neglect,
   BK=@Bk,
   YeBan=@YeBan,
   TongXiao=@TongXiao,
   WX=@WX,
   CC=@cc,
   BJ=@BJ,
   SJ=@SJ,
   HJ=@HJ,
   SaJ=@Saj,
   GSJ=@GSJ,
   NJ=@NJ,
   CJ=@CJ,
   PCJ=@PCJ,
   YGFJ=@YGFJ,
   BRJ=@BRJ,
   AdjustRest = @AdjustRest,
   AppOWHours=@OWApply,OWAbnormFlag=0
   where StaffNo=@StaffNo and Datediff(day,FDate,@Date)=0
  else
   insert into WorkDayRecord(StaffNo,FDate,ShiftID,OnDuty1,OffDuty1,OnDuty2,OffDuty2,OnDuty3,OffDuty3,ShouldTime,RealTime,LateEarly,LateNum,Neglect,BK,YeBan,TongXiao,OverWork1,OverWork2,OverWork3,WX,AdjustRest,
   CC,BJ,SJ,HJ,SaJ,GSJ,NJ,CJ,PCJ,YGFJ,BRJ,AppOWHours,OWAbnormFlag
   )
   values (@StaffNo,@Date,@ShiftID,@OnDuty1,@OffDuty1,@OnDuty2,@OffDuty2,@OnDuty3,@OffDuty3,@ShouldTime,@RealTime,@LateEarly,@LateNum,@Neglect,@BK,@YeBan,@TongXiao,@OW1,@OW2,@OW3,@WX,@AdjustRest,
   @CC,@BJ,@SJ,@HJ,@SaJ,@GSJ,@NJ,@CJ,@PCJ,@YGFJ,@BRJ,@OWApply,0)

  if object_id('tempdb..#t2') is not null
   drop table #t2
  if object_id('tempdb..#t3') is not null
   drop table #t3
 end
 
goOn:
 drop table #t1
 
 set @date = dateAdd(day,1,@date)
end
-----------------更新月报表---------------
set @date = dateAdd(day,-1,@date)

select @OW1 = sum(isNull(OverWork1,0)),@OW2=sum(isNull(OverWork2,0)),@OW3=sum(isNull(OverWork3,0)) ,@AdjustRest=sum(IsNull(AdjustRest,0))
from WorkDayRecord
where StaffNo=@StaffNo and Datediff(month,@date,FDate)=0
group by StaffNo

if @AdjustRest>0 and (left(@OrgCode,3) <>'002' or (left(@OrgCode,3)='002' and @SalaryMode='002') )
begin
 set @temp = @AdjustRest --* 60
 if  @temp > 0
 begin
  declare curA cursor for
  select FDate from EmployeeLeave where HolidayType='011' and StaffNo=@StaffNo and datediff(month,@date,FDate)=0 and Status=3
  open curA  
  fetch next from curA into @edate
  while @@FETCH_STATUS=0
  begin

   set @i=null
   select @i = AdjustRest from WorkDayRecord where StaffNo=@StaffNo and Fdate=@edate   
  
   if @i is not null --and @temp >= isNull(@i,0)
   and not exists (select 1 from EmployeeLeave a inner join WorkRepair b on a.LeaveID=b.leaveID where staffno=@staffno and a.fdate=@edate and a.WholeDay=3)
   begin
    
    
    update WorkDayRecord set RealTime = case when isnull(RealTime,0) + (AdjustRest/60.0) >8 then 8 else isnull(RealTime,0) + (AdjustRest/60.0) end
     where StaffNo=@StaffNo and Fdate=@edate
    set @temp = @temp - @i
    if @temp<=0
     break
   end
   
   fetch next from curA into @edate   
  end
  close curA
  deallocate curA
 end
 set @AdjustRest = @AdjustRest/60
 --聚能调休先扣2倍加班

 if left(@OrgCode,3) in ('005','010')
 begin
  --先从2倍加班扣调休,如果2倍加班>=调休时间,则将加班减去调休时间,调休置为0
  if @OW2 >= @AdjustRest
  begin
   set @OW2=@OW2-@AdjustRest
   set @AdjustRest=0
  end
  else
  begin--如果2加班时间小于调休时间,则将调休时间减去2倍加班时间,2倍加班时间置0
   set @AdjustRest=@AdjustRest-@OW2
   set @OW2=0
   --判断1.5倍加班时间是否>=调休时间,原则同上 
   if @OW1>=@AdjustRest
   begin
    set @OW1=@OW1-@AdjustRest
    set @AdjustRest=0
   end
   else
   begin
    set @AdjustRest=@AdjustRest-@OW1
    set @OW1=0
    
   end
  end
 end
 else
 begin
  --先从1.5倍加班扣调休,如果1.5倍加班>=调休时间,则将加班减去调休时间,调休置为0
  if @OW1 >= @AdjustRest
  begin
   set @OW1=@OW1-@AdjustRest
   set @AdjustRest=0
  end
  else
  begin--如果1.5加班时间小于调休时间,则将调休时间减去1.5倍加班时间,1.5倍加班时间置0
   set @AdjustRest=@AdjustRest-@OW1
   set @OW1=0
   --判断2倍加班时间是否>=调休时间,原则同上  
   if @OW2>=@AdjustRest
   begin
    set @OW2=@OW2-@AdjustRest
    set @AdjustRest=0
   end
   else
   begin
    set @AdjustRest=@AdjustRest-@OW2
    set @OW2=0
   end
  end

 end
 if @AdjustRest > 0
 begin
  if @OW3>=@AdjustRest
  begin
   set @OW3=@OW3-@AdjustRest
   set @AdjustRest=0
  end
  else
  begin
   set @AdjustRest=@AdjustRest-@OW3
   set @OW3=0
  end
 end

 
end

 

select
@OW1=@OW1 + isNull(sum(isNull(OW1,0)),0),
@OW2=@OW2 + isNull(sum(isNull(OW2,0)),0),
@OW3=@OW3 + isNull(sum(isNull(OW3,0)),0),
@Neglect = @Neglect + isNull(sum(isNull(Neglect,0)),0) from OverworkAdjust

where staffno=@staffno and datediff(month,fdate,@date)=0 and Status=3

if @DutyLevel<=3 and @DutyLevel<>0 and @SalaryMode='002'
 select @OW1=0,@OW2=0,@OW3=0

 

declare cur cursor for

select a.FDate,b.FDate
from employeeLeave a
inner join workRepair b on a.LeaveID=b.LeaveID
left join employees e on a.staffno=e.staffno
where a.staffno=@Staffno and datediff(month,a.fdate,@Date)=0
and a.Status=3
and exists (select 1 from publicHoliday
where HolidayType='001'
and e.OrgCode like OrgCode + '%'
and e.SalaryMode=SalaryMode
and charindex(e.Gender,Gender)>0 and HolidayDate=b.FDate)

open cur
fetch next from cur into @OnDuty,@OffDuty

while @@Fetch_status=0
begin

 insert into workDayRecord(staffno,Fdate,ShouldTime,RealTime)
 select @staffno,@OnDuty,ShouldTime,RealTime from WorkDayRecord where staffno=@Staffno and FDate=@OffDuty

 update WorkDayRecord
 set ShouldTime=8,RealTime=8 where Staffno=@Staffno and FDate=@OffDuty

 fetch next from cur into @OnDuty,@OffDuty
end
close cur
deallocate cur

if  (@SalaryFlag=2 and @WorkFlag=0)
begin
 if exists (select 1 from workMonth where staffno=@Staffno and datediff(month,fdate,@date)=0)
 delete from workMonth where staffno=@Staffno and datediff(month,fdate,@date)=0

 return
end

select @AdjustRest
select StaffNo,
DATEADD(day,-day(@date)+1,@date) as 月份,
sum(case isNull(ShouldTime,0) when 0 then 0 else 1 end) as 应勤,
--sum(realtime) as 实勤,

sum(case isNull(ShouldTime,0) when 0 then 0 else cast( isNull(RealTime,0)/ShouldTime as decimal(18,4)) end)
-  case when left(@OrgCode,3) ='002' and @SalaryMode='001' then 0 else @AdjustRest/8 end  as 实勤,

SUM(isNull(LateEarly,0))  as 迟到,
SUM(isnull(LateNum,0)) as 迟到次数,
SUM(isNull(Neglect,0))  as 旷工 ,

@OW1 as OW1,
@OW2 as OW2,
@OW3 as OW3,
SUM(isNull(CC,0))/8 as cc,
SUM(isNull(BJ,0))/8 as bj,
SUM(isNull(SJ,0))/8 as sj,
SUM(isNull(HJ,0))/8 as hj,
SUM(isNull(SaJ,0))/8 as saj,
SUM(isNull(GSJ,0))/8 as gsj,
SUM(isNull(CJ,0))/8 as cj,
SUM(isNull(PCJ,0))/8 as pcj,
SUM(isNull(YGFJ,0))/8 as ygfj,
SUM(isNull(NJ,0))/8 as nj,
sum(isNull(WX,0)+ case when left(@OrgCode,3) ='002' and @SalaryMode='001' then AdjustRest/60  else 0 end) /8+case when left(@OrgCode,3) ='002' and @SalaryMode='001' then 0  else @AdjustRest/8 end    as 无薪,
sum(isNull(BK,0)) as 补卡,
sum(isNull(YeBan,0)) as 夜班,
sum(isNull(TongXiao,0)) as 通宵,

@EatSum as 餐费,
@maxDate as MaxDate
into #tMonth
from WorkDayRecord 
where StaffNo=@StaffNo
and Datediff(month,@date,FDate)=0
group by StaffNo

 

--更新月报表
if not exists(select 1 from workmonth where staffno=@staffno and datediff(month,fdate,DATEADD(day,-day(@date)+1,@date))=0)
 insert into workmonth (
 StaffNo,FDate,ShouldTime,RealTime,LateEarly,LateNum,Neglect,OverWork1,OverWork2,OverWork3,
 CC,BJ,SJ,HJ,SaJ,GSJ,CJ,PCJ,YGFJ,NJ,WX,BK,YeBan,TongXiao,EatTotal,LastDate)
 select * from #tMonth
else
 update workmonth
 set shouldtime=a.应勤,realtime=a.实勤,
 lateEarly=a.迟到,lateNum=a.迟到次数,Neglect=a.旷工,OverWork1=OW1,OverWork2=OW2,OverWork3=OW3,
 cc=a.cc,bj=a.bj,sj=a.sj,hj=a.hj,saj=a.saj,gsj=a.gsj,cj=a.cj,pcj=a.pcj,ygfj=a.ygfj,nj=a.nj,wx=a.无薪,
 bk=a.补卡,yeban=a.夜班,tongxiao=a.通宵,EatTotal=a.餐费,LastDate=a.MaxDate
 from #tMonth a inner join workmonth w on a.staffno=w.staffno and a.月份=w.fdate

drop table #tMonth

set nocount off

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Answer 5

你这也太夸张了吧。。。

---

it's time to start living the life you are imagined.

Answer 6

没办法啊，确实有这么多的代码啊。

Answer 7

你好，

这条语句，set @temp = @temp /30 * 30 是把结果按30取整来算的,精确到30分钟。当@temp<30 时，@temp = 0; 你可以测试一下下面的代码，来证实一下：

declare @temp int
set @temp = 29
select @temp/30*30,@temp
set @temp = 31
select @temp/30*30,@temp

 

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 8

你好，

这条语句，set @temp = @temp /30 * 30 是把结果按30取整来算的,精确到30分钟。当@temp<30 时，@temp = 0; 你可以测试一下下面的代码，来证实一下：

 

declare @temp int
set @temp = 29
select @temp/30*30,@temp
set @temp = 31
select @temp/30*30,@temp

 

 

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

我试过将所有

set @temp = @temp /30 * 30
改为

set @temp = @temp/(30+0.0)*30

还是不行。

Answer 9

请问这条语句  set @temp = @temp/(30+0.0)*30 是想完成什么操作？

如果是像精确到一分钟的话，试试看用@temp，按照他的模式一分钟公式是这样的：set @temp = @temp/1*1.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 10

还是不行。改成@temp/1*1也不行。
改成@temp/(30+0.0)*30

是想计算小数点后面的。因为29/(30+0.0)*30就不会等于0了。

Answer 11

改成@temp/(30+0.0)*30 是想计算小数点后面的。因为29/(30+0.0)*30就不会等于0了。

请问@temp 的数据类型是float吗？ 如果是int的话，会忽略小数点后的数据的。

 

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 12

@temp  int,
看他的定义应该是整数型的。都不知道该怎么改了。

Answer 13

真是牛人。。。。

@Temp/30*30，也就是按每30算一个单位

用CURSOR来计算，若一个企业有一万多人，那服务器就必须勤劳勇敢了。。。

---

Try SQL Server 2008 QQ:315054403 [e-mail]

Answer 14

真是牛人。。。。

@Temp/30*30，也就是按每30算一个单位

用CURSOR来计算，若一个企业有一万多人，那服务器就必须勤劳勇敢了。。。

---

Try SQL Server 2008 QQ:315054403 [e-mail]

呵呵，加上杂七杂八的人，也才10000左右罢了，很大的运算量吗？