如果是想让系统自动定时抓取资唯迅料可以创建一个jobs。指定时答山搏间让它自动执行。
如果是说想知道那清祥些人没有刷卡。
select * from 人员信息表 where 员工id not in （
select 员工id from 打卡表 where 时间范围条件
）
就可以了。
首先要有一个工厂日历的表，列出所拆庆有工作日,至少一个源岩字段：工作日 varchar(10)。然后这样即可：
select id,
迟到次数=sum(case when timec>'08:00:00' tand timec<'09:00:00' then 1 else 0 end),
旷工次数=sum(case when timec>'09:00:00' or timec is null then 1 else 0 end),
打卡次数=sum(case when timec is null then 0 else 1 end)
from
(
SELECT * FROM 工厂日历旅裂握 left join
(select id,
datec=convert(varchar(10),card_time,120),
timec=substring(convert(varchar,card_time,120),12,8)
from tablename
) a
on 工作日=DATEC
) b
group by ID
select A1.card_no,
A1.atte_time as "上班时间",
A2.atte_time as "上班时间1",
A3.atte_time as "上班时间2",
A4.atte_time as "上班时间3",
B1.atte_time as "下班时间",
B2.atte_time as "下班时间1",
B3.atte_time as "下班时间2",
B4.atte_time as "下班时间3"
from (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID
from attetime
where doorinout = 1) T
where T.In_ID = 1) A1
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID
from attetime
where doorinout = 1) T
where T.In_ID = 2) A2
on A1.card_no = A2.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID
from attetime
where doorinout = 1) T
where T.In_ID = 3) A3
on A1.card_no = A3.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID
from attetime
where doorinout = 1) T
where T.In_ID = 4) A4
on A1.card_no = A4.card_no
full join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID
from attetime
where doorinout = 0) T
where T.Out_ID = 1) B1
on A1.card_no = B1.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition 迟配滑by card_no order by atte_time desc) as Out_ID
from attetime
where doorinout = 0) T
where T.Out_ID = 2) B2
on A1.card_no = B2.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID
from attetime
where doorinout = 0) T
where T.Out_ID = 3) B3
on A1.card_no = B3.card_no
left join (select card_no, atte_time
from (select 码腊card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID
from attetime
where 卖码doorinout = 0) T
where T.Out_ID = 4) B4
on A1.card_no = B4.card_no
投入验证数据如下：
执行结果如下：
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）