MSSQL 連續出勤（二）

接著上次的，那個查詢最近連續出勤天數：取最大的day_cha

select a.* from (select t2.empno, t2.month,abs(t2.day_cha) day_cha,

count(t2.day_cha) days

from

(select t.empno,t.emptype,t.deptcode,t.attdate,left(t.attdate,6) as month ,t.OnTime,t.date_rank,day(t.OnTime)-t.date_rank as day_cha

from (

    select A.emptype,A.empno,A.deptcode,A.attdate,A.OnTime,A.ShiftTime,row_number() over(partition by A.empno order by A.OnTime) date_rank

from

   TB1 A,TB2 B where A.empno=B.Emplid and B.Udate=convert(nvarchar(11),getdate()-1,112)and 

ontime is not null and ShiftTime is not null)t

) t2 group by empno,day_cha,month ) as a where day_cha = (select max(tt.day_cha) from (select t2.empno, t2.month,abs(t2.day_cha) day_cha,

count(t2.day_cha) days

from

(select t.emptype, t.empno,t.deptcode,t.attdate,left(t.attdate,6) as month ,t.OnTime,t.date_rank,day(t.OnTime)-t.date_rank as day_cha

from (

    select A.emptype,A.empno,A.deptcode,A.attdate,A.OnTime,A.ShiftTime,row_number() over(partition by A.empno order by A.OnTime) date_rank

from

    TB1 A,TB2 B where A.empno=B.Emplid and B.Udate=convert(nvarchar(11),getdate(),112)  and 

ontime is not null and ShiftTime is not null)t

) t2 group by empno,day_cha,month )tt where a.empno=empno)

1

如圖一所示：能看到查詢的是在崗的所有人的最後一次連續的出勤天數，但是通過取day_cha有弊端

2

在一個月初的day_cha比當月都大，導致多月數據取比對，得到的不是最近一次：所有最好取當月的，但是在換月的時候又有問題：

select a.* from (select t2.empno, t2.month,abs(t2.day_cha) day_cha,

count(t2.day_cha) days

from

(select t.empno,t.emptype,t.deptcode,t.attdate,left(t.attdate,6) as month ,t.OnTime,t.date_rank,day(t.OnTime)-t.date_rank as day_cha

from (

    select A.emptype,A.empno,A.deptcode,A.attdate,A.OnTime,A.ShiftTime,row_number() over(partition by A.empno order by A.OnTime) date_rank

from

    TB1 A,TB2 B where A.empno=B.Emplid and B.Udate=convert(nvarchar(11),getdate()-1,112)and 

ontime is not null and ShiftTime is not null and datediff(month,A.ShiftTime,getdate())=0)t

) t2 group by empno,day_cha,month ) as a where day_cha = (select max(tt.day_cha) from (select t2.empno, t2.month,abs(t2.day_cha) day_cha,

count(t2.day_cha) days

from

(select t.emptype, t.empno,t.deptcode,t.attdate,left(t.attdate,6) as month ,t.OnTime,t.date_rank,day(t.OnTime)-t.date_rank as day_cha

from (

    select A.emptype,A.empno,A.deptcode,A.attdate,A.OnTime,A.ShiftTime,row_number() over(partition by A.empno order by A.OnTime) date_rank

from

   TB1 A,TB2 B where A.empno=B.Emplid and B.Udate=convert(nvarchar(11),getdate(),112)  and 

ontime is not null and ShiftTime is not null and  datediff(month,A.ShiftTime,getdate())=0)t

) t2 group by empno,day_cha,month )tt where a.empno=empno)