with a as ( select empid, recdate, min(rectime) t1 from tb where rectime<'12:00' group by empid, recdate -- 上午有效数据 ), b as ( select empid, recdate, min(rectime) t2 from tb where rectime>='12:00' group by empid, recdate --下午有效数据 ), c as ( select isnull(a.empid,b.empid) empid, isnull(a.recdate,b.recdate) recdate, t1, t2 from a full join b on a.emplid=b.emplid and a.recdate=b.recdate ) select * from c where isnull(t1,'12:00')>'08:00' or isnull(t2,'12:00')<'17:00' -- 显示问题就是case when了,组合太多我不一一帮你枚举。 另外要根据排班表才能找出旷工(没打卡)数据
(
select empid, recdate, min(rectime) t1 from tb where rectime<'12:00' group by empid, recdate -- 上午有效数据
), b as
(
select empid, recdate, min(rectime) t2 from tb where rectime>='12:00' group by empid, recdate --下午有效数据
), c as
(
select isnull(a.empid,b.empid) empid, isnull(a.recdate,b.recdate) recdate, t1, t2
from a full join b on a.emplid=b.emplid and a.recdate=b.recdate
)
select * from c where isnull(t1,'12:00')>'08:00' or isnull(t2,'12:00')<'17:00'
-- 显示问题就是case when了,组合太多我不一一帮你枚举。
另外要根据排班表才能找出旷工(没打卡)数据