要命啊。///这么长。。看样子中秋的假是完了
Select K.*,A.OverTotal From
(Select empl_Code,Sum(Case When OverTime Is Null Then 0 Else OverTime End) As OverTotal From
(Select A.*,B.NoonIn,B.NoonOut,C.NightIn,C.NightOut,Convert(Decimal(9,2),Convert(Decimal(9,2),DateDiff(Mi,MorningIn,MorningOut) +
DateDiff(Mi,NoonIn,NoonOut) + DateDiff(Mi,NightIn,NightOut))/60) - 8 As Overtime From
(Select Attendance.empl_Code,NowDate,
Min(Case When Convert(char(8),DHTime,8) Between '07:35:00' And '08:30:00' --Ν痁秈
Then '08:00:00' End) As MorningIn,
Max(Case When Convert(char(8),DHTime,8) Between '11:56:00' And '12:15:00' --Ν痁?
Then '12:00:00' End ) As MorningOut From Attendance
Left Join Bhr on Attendance.empl_Code=Bhr.empl_Code
Where Attendance.Dept_Name=Bhr.Dept_Name And Bhr.Group_Name= 'ó?┬' And DatePart(dw,NowDate) Between 2 And 6
Group By Attendance.empl_Code,NowDate) As A
Left Join
(Select Attendance.empl_Code,NowDate,
Min(Case When Convert(char(8),DHTime,8) Between '13:00:00' And '13:35:00' --い痁秈
Then '13:30:00' End) As NoonIn,
Max(Case When Convert(char(8),DHTime,8) Between '17:20:00' And '17:45:00' --い痁?
Then '17:30:00' End ) As NoonOut From Attendance
Left Join Bhr on Attendance.empl_Code=Bhr.empl_Code
Where Attendance.Dept_Name=Bhr.Dept_Name And Bhr.Group_Name= 'ó?┬' And DatePart(dw,NowDate) Between 2 And 6
Group By Attendance.empl_Code,NowDate ) As B on A.empl_Code=B.empl_Code
And A.NowDate=B.NowDate
Left Join
(Select Attendance.empl_Code,NowDate,
Min(Case When Convert(char(8),DHTime,8) Between '18:15:00' And '18:35:00' --边痁秈
Then '18:30:00' End) As NightIn,
Max(Case When Convert(Char(8),DHTime,8) > '18:30:00' Then --边痁?
CASE When datepart(mi, DHTime) Between 0 AND 24
Then Convert(Char(8),RIGHT('00' + RTRIM(Convert(Char(2),DatePart(hh, DHTime))),2) + ':' + '00:00' ,8)
When datepart(mi, DHTime) BETWEEN 25 AND 54
Then Convert(Char(8), RIGHT('00' + RTRIM(Convert(Char(2),DatePart(hh, DHTime))),2) + ':' + '30:00' ,8)
Else Convert(Char(8),DateAdd(mi, 60 - Datepart(mi,dhtime),DHTime))
End
End ) As NightOut From Attendance
Left Join Bhr on Attendance.empl_Code=Bhr.empl_Code
Where Attendance.Dept_Name=Bhr.Dept_Name And Bhr.Group_Name= 'ó?┬' And DatePart(dw,NowDate) Between 2 And 6
Group By Attendance.empl_Code,NowDate ) As C
On A.empl_Code=C.empl_Code And A.NowDate=C.NowDate
) As H Group By empl_Code) As A
Left Join
(Select A.*,B.NoonIn,B.NoonOut,C.NightIn,C.NightOut,Convert(Decimal(9,2),Convert(Decimal(9,2),DateDiff(Mi,MorningIn,MorningOut) +
DateDiff(Mi,NoonIn,NoonOut) + DateDiff(Mi,NightIn,NightOut))/60) - 8 As Overtime From
(Select Attendance.empl_Code,NowDate,
Min(Case When Convert(char(8),DHTime,8) Between '07:35:00' And '08:30:00' --Ν痁秈
Then '08:00:00' End) As MorningIn,
Max(Case When Convert(char(8),DHTime,8) Between '11:56:00' And '12:15:00' --Ν痁?
Then '12:00:00' End ) As MorningOut From Attendance
Left Join Bhr on Attendance.empl_Code=Bhr.empl_Code
Where Attendance.Dept_Name=Bhr.Dept_Name And Bhr.Group_Name= 'ó?┬' And DatePart(dw,NowDate) Between 2 And 6
Group By Attendance.empl_Code,NowDate) As A
Left Join
(Select Attendance.empl_Code,NowDate,
Min(Case When Convert(char(8),DHTime,8) Between '13:00:00' And '13:35:00' --い痁秈
Then '13:30:00' End) As NoonIn,
Max(Case When Convert(char(8),DHTime,8) Between '17:20:00' And '17:45:00' --い痁?
Then '17:30:00' End ) As NoonOut From Attendance
Left Join Bhr on Attendance.empl_Code=Bhr.empl_Code
Where Attendance.Dept_Name=Bhr.Dept_Name And Bhr.Group_Name= 'ó?┬' And DatePart(dw,NowDate) Between 2 And 6
Group By Attendance.empl_Code,NowDate ) As B on A.empl_Code=B.empl_Code
And A.NowDate=B.NowDate
Left Join
(Select Attendance.empl_Code,NowDate,
Min(Case When Convert(char(8),DHTime,8) Between '18:15:00' And '18:35:00' --边痁秈
Then '18:30:00' End) As NightIn,
Max(Case When Convert(Char(8),DHTime,8) > '18:30:00' Then --边痁?
CASE When datepart(mi, DHTime) Between 0 AND 24
Then Convert(Char(8),RIGHT('00' + RTRIM(Convert(Char(2),DatePart(hh, DHTime))),2) + ':' + '00:00' ,8)
When datepart(mi, DHTime) BETWEEN 25 AND 54
Then Convert(Char(8), RIGHT('00' + RTRIM(Convert(Char(2),DatePart(hh, DHTime))),2) + ':' + '30:00' ,8)
Else Convert(Char(8),DateAdd(mi, 60 - Datepart(mi,dhtime),DHTime))
End
End ) As NightOut From Attendance
Left Join Bhr on Attendance.empl_Code=Bhr.empl_Code
Where Attendance.Dept_Name=Bhr.Dept_Name And Bhr.Group_Name= 'ó?┬' And DatePart(dw,NowDate) Between 2 And 6
Group By Attendance.empl_Code,NowDate ) As C
On A.empl_Code=C.empl_Code And A.NowDate=C.NowDate ) As K
On A.empl_Code=K.empl_Code Where K.NowDate Between Convert(Char(8),GetDate(),120) + '01' And Convert(Char(10),GetDate(),120)
Order By A.empl_Code
我们老大叫我看。。