17,078
社区成员
发帖
与我相关
我的任务
分享
with
mm as (select to_date(&mon||'01','YYYYMMDD')+ rownum-1 mday from dual connect by rownum <=31),
yuangong as (select '员工1' yg from dual
union all
select '员工2' yg from dual
union all
select '员工3' yg from dual
),
kaoqing as (select '员工1' yg,to_date('201001010812', 'YYYYMMDDHH24MISS') tt from dual
union all
select '员工1' yg,to_date('201001011612', 'YYYYMMDDHH24MISS') tt from dual
union all
select '员工2' yg,to_date('201001020712', 'YYYYMMDDHH24MISS') tt from dual ),
my as ( select * from mm,yuangong where to_char(mday,'YYYYMM')= &mon),
kqs as (select yg,to_date(to_char(tt,'YYYYMMDD'),'YYYYMMDD')mday,min(tt) mintt,max(tt)maxtt from kaoqing group by yg,to_char(tt,'YYYYMMDD'))
select my.mday,my.yg, mintt,maxtt,
case when mintt is null then '未打卡'
when mintt >my.mday+8/24 and maxtt <my.mday+17/24 then '迟到早退'
when mintt >my.mday+8/24 then '迟到'
when maxtt <my.mday+17/24 then '早退'
else null
end
from my,kqs
where kqs.mday(+)=my.mday
and kqs.yg(+)=my.yg