--按照每个周期来算.日期已经是周期
Declare @StartDate as Datetime --开始日期
Declare @EndDate as DateTime --结束日期,通过开始日期计算得到
Declare @Long as int --连续查多少个月
--创建临时表，保存结果
Create Table #TotalOT
(
代码 varchar(20),
姓名 varchar(20),
部门 varchar(50),
小时数 int,
周期 varchar(40)
)
--根据传入的职员条件，获得职员信息，包括职员ID，周期内周一到周四的个数，周期内周六的个数
Create Table #EmpInfo
( xEmpID int,MonToThurNum int ,SatNum int )
Insert Into #EmpInfo (xEmpID)
Select xID
From xEmp_Entry
Where 1 = 1
Set @Long =2
Declare @index as int
Set @index = 0
while @index < (@long +1)
Begin
Set @StartDate = '2007-01-01'
Set @StartDate = DateAdd(month, @index,@StartDate)
Set @StartDate = DateAdd(day,16,@StartDate)
Set @EndDate = DateAdd(month,1,@StartDate)
Set @EndDate = DateAdd(day,-1,@EndDate)
Set @index = @index + 1
--具体算法
--得到四天一休的班次ID
-- Drop table #Class41ID
Select C.xClassID
Into #Class41ID
From xKq_Class C
Inner Join xKq_ClassEntry CE on CE.xClassID = C.xClassID
Where xName like '%四天一休%'
-- Drop table #MonToThur
--周期内有多少个周一到周四
Declare @tempDate as dateTime
Declare @i as int
Declare @SaturdayCount as int
Declare @MonToThurCount as int
Set @i = 0
Set @SaturdayCount = 0
Set @MonToThurCount = 0
Set @tempDate = DateAdd(day,@i,'2007-01-17')
while @tempDate <= '2007-02-16'
Begin
if DateName(weekday,@tempDate) <> '星期六' and DateName(weekday,@tempDate) <> '星期日'
Begin
Set @MonToThurCount = @MonToThurCount + 1
End
else
Begin
Set @SaturdayCount = @SaturdayCount + 1
End
Set @i = @i +1
Set @tempDate = DateAdd(day,@i,'2007-01-17')
End
Update #EmpInfo
Set MonToThurNum = @MonToThurCount,
SatNum = @SaturdayCount
--获得工作周期内，职员上班的次数
Select count(1) as Num,WC.xEmpID
Into #EmpOnClass41
From xKq_WorkCheck WC
Inner Join #Class41ID CI on CI.xClassID = WC.xClassID
Inner Join #EmpInfo EI on EI.xEmpID = WC.xEmpID
Where cast((cast(xYear as varchar(4)) + '-' + cast(xMonth as varchar(2)) + '-' +cast(xDay as varchar(2))) as datetime) between '2007-01-17' and '2007-02-16'
Group by WC.xEmpID
--考虑请假的情况，获得周期内的这些职员的请假次数
Select
Beg.xEmpID,xBegType,xStarDate,xEndDate,
Case
When xCheckLong is null then 0.00001
When xCheckLong = 0 then 0.00001
Else xCheckLong
End as xCheckLong,
DateDiff(day,xStarDate,xEndDate) +1 as xTotalLong,
Case
when DateDiff(day,xStarDate,'2007-01-17') < 0 then 0
else DateDiff(day,xStarDate,'2007-01-17')
End as Beginning,
case
When DateDiff(day,'2007-02-16',xEndDate) < 0 then 0
else DateDiff(day,'2007-02-16',xEndDate)
End as Stop
Into #Beg
From xkq_Beg Beg
Inner Join #EmpInfo EI on EI.xEmpID = Beg.xEmpID and Beg.xBegType = '请假'
Where xChecked = '是'
and ( '2007-01-17' Between xStarDate and xEndDate
or '2007-02-16' Between xStarDate and xEndDate
or ('2007-01-17' < xStarDate and '2007-02-16' > xEndDate) )
----------------------------------------------不完全为四天一休的处理----------------------------------------------------
--获得周期内工作全部是四天一休的职员信
Select EOC.xEmpID,EI.SatNum
Into #EmpAllonClass41
From #EmpOnClass41 EOC
Inner Join #EmpInfo EI on EI.xEmpID = EOC.xEmpID and EI.MonToThurNum = EOC.Num
插入周期内全部是4-1班的职员的信息
Insert Into #TotalOT
Select
Emp.代码,Emp.姓名,Emp.部门,
EAOC.SatNum * 4 - Cast((isnull(xTotalLong,0) - IsNull(Beginning,0) - IsNull(Stop,0)) as float)/Cast(xCheckLong as float) as 小时数,
'2007-01-17' + '到' + '2007-02-16' as 周期
From #EmpAllOnClass41 EAOC
Inner Join xEmp_Entry Emp on Emp.xID = EAOC.xEmpID
Left Join #Beg Beg on Beg.xEmpID = EAOC.xEmpID
------------------------------------------------不完全为四天一休的处理----------------------------------------------------
--获得周期内工作为不完全是“四天一休”的职员
Select distinct WC.xEmpID
Into #EmpFewOnClass41
From xKq_WorkCheck WC
Inner Join #EmpInfo EI on EI.xEmpID = WC.xEmpID
Left Join #EmpAllonClass41 EAOC on EAOC.xEmpID = WC.xEmpID
Left Join #Class41ID CI on CI.xClassID = WC.xClassID
where cast((cast(xYear as varchar(4)) + '-' + cast(xMonth as varchar(2)) + '-' +cast(xDay as varchar(2))) as datetime) between '2007-01-17' and '2007-02-16'
and CI.xClassID is not null and EAOC.xEmpID is null
Select WS.xEmpID,Sum(isnull(WC.xLong,0)) as OTNum
Into #EmpOverTime
From #EmpFewOnClass41 EFOC
Left Join xKq_WorkCheck WC on WC.xEmpID = EFOC.xEmpID
Left Join xKq_WorkSet WS on WS.xEmpID = EFOC.xEmpID
and WS.xYear = WC.xYear and WS.xMonth = WC.xMonth and WS.xDay = WC.xDay
and WS.xClassID = WC.xClassID
Left Join xKq_ClassEntry CE on CE.xClassID = WC.xClassID and CE.xOverTime = '1'
Where WC.xClassID not in ( Select xClassID From #Class41ID )
and cast((cast(WC.xYear as varchar(4)) + '-' + cast(WC.xMonth as varchar(2)) + '-' +cast(WC.xDay as varchar(2))) as datetime) between '2007-01-17' and '2007-02-16'
Group by WS.xEmpID
Select
Emp.代码,Emp.姓名,Emp.部门,
Case
When EI.MonToThurNum - isnull(EOT.OTNum,0) - Cast((isnull(xTotalLong,0) - IsNull(Beginning,0) - IsNull(Stop,0)) as float)/Cast(xCheckLong as float) < 0 then 0
When EI.MonToThurNum - isnull(EOT.OTNum,0) - Cast((isnull(xTotalLong,0) - IsNull(Beginning,0) - IsNull(Stop,0)) as float)/Cast(xCheckLong as float) >16 then 16
else isnull((EI.MonToThurNum - isnull(EOT.OTNum,0) - Cast((isnull(xTotalLong,0) - IsNull(Beginning,0) - IsNull(Stop,0)) as float)/Cast(xCheckLong as float)),0)
End as 小时数,
'2007-01-17' + '到' + '2007-02-16' as 周期
From #EmpFewOnClass41 EFOC
Inner Join #EmpInfo EI on EI.xEmpID = EFOC.xEmpID
Left Join #Beg Beg on Beg.xEmpID = EFOC.xEmpID
Left Join #EmpOverTime EOT on EOT.xEmpID = EFOC.xEmpID
Inner Join xEmp_Entry Emp on Emp.xID = EFOC.xEmpID
end
浙公网安备 33010602011771号