USE 数据库
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function 函数名称
(@EmpID nvarchar(50))
returns nvarchar(10)
as
begin
DECLARE
@TempDayID NVARCHAR(10), --費用計算日
@TempDayID1 NVARCHAR(10), --最後一天請非曠工假
@TempDayID2 NVARCHAR(10), --最後一天上班日（非責任制）
@VocaDayID NVARCHAR(10),
@VocaEmpID NVARCHAR(50),
@VocaTotalHours NUMERIC(7, 2),
@IsDuty NVARCHAR(1)
--查找離職人員请的最大非旷工假的請假日期
SELECT @TempDayID1=MAX(VocaDayID) FROM temp临时表 WHERE VocaEmpID=@EmpID AND ColType!='C04'
--查找離職人員的請假明細
SELECT @VocaEmpID=VocaEmpID,@VocaDayID=VocaDayID,@VocaTotalHours=VocaTotalHours ,@IsDuty=IsDuty
FROM temp临时表
WHERE VocaEmpID=@EmpID AND VocaDayID =@TempDayID1
--查找離職人員的最后上班日
SELECT @TempDayID2= MAX(A.DayID)
FROM 出勤表 A, 离职表签核明细表 B
WHERE A.EmpID=B.EmpID AND A.IsDuty='N' AND B.EmpID=@EmpID AND A.IsClass='Y' AND A.ABMinutes >=560
IF (@TempDayID IS NULL)
BEGIN
SET @TempDayID = CONVERT(NVARCHAR(10),DATEADD(DAY,0,@TempDayID1),111)
IF (@TempDayID1<@TempDayID2)
BEGIN
SET @TempDayID=@TempDayID2
END
ELSE IF(@TempDayID1>@TempDayID2)
BEGIN
IF(@VocaTotalHours<8)
BEGIN
SET @TempDayID= CONVERT(NVARCHAR(10),DATEADD(DAY,-1,@TempDayID1),111)
END
ELSE
BEGIN
SET @TempDayID=@TempDayID1
END
END
END
return @TempDayID
end