【SQL开源代码栏目提醒】:网学会员,鉴于大家对SQL开源代码十分关注,论文会员在此为大家搜集整理了“GetAbsentAndLate.sql”一文,供大家参考学习!
create procedure %PROC% (In iEmployeeID Integer,
IN dtStartDate,In dtEndDate,Out iLateTimes Integer,
Out iAbsentTimes)
begin
Declare dtThis Date
Set dtThis = dtStartDate
Set iLateTimes =0
Set iAbsentTimes =0
While DateDiff(Day,dtThisDate,dtEndDate) > -1 Loop
IF (Select Count(*) From AssignDuty Where DateDiff(Day,dtStart,dtThisDate)>-1 and DateDiff(Day,dtThisDate,dtEnd)>-1 AND iDutyID=iEmployeeID) <1 then
IF (Select Count(*) From Check Where DateDiff(Day,dtDate,dtThisDate)=0 AND iDutyID=iEmployeeID) <1 Then
iAbsentTimes = iAbsentTimes +1
ELSE
IF (Select Count(*) From Check Where DateDiff(Day,dtDate,dtThisDate)=0 AND iDutyID=iEmployeeID AND (Hour(timeCheckCome)>9 Or Hour(timeCheckLeave)<17) ) <1 Then
iLateTimes = iLateTimes +1
END IF
END IF
END IF
End Loop
end
/