34,838
社区成员




create table #tmp(empid int ,AttnDate Datetime, Fstyle Int)
insert into #tmp
select 1,'2008-01-02 09:00:01',0
union all
select 1,'2008-01-02 09:00:15',0
union all
select 1,'2008-01-02 09:00:30',0
union all
select 1,'2008-01-02 12:00:02',1
union all
select 1,'2008-01-02 14:00:00',0
union all
select 1,'2008-01-02 17:35:00',1
delete t
from #tmp t
where exists (
select 1 from #tmp
where empid=t.empid and Fstyle=t.Fstyle
and AttnDate<t.AttnDate
and datediff(mi,AttnDate,t.AttnDate)<5
)
select * from #tmp
--结果
empid AttnDate Fstyle
----------- ------------------------------------------------------ -----------
1 2008-01-02 09:00:01.000 0
1 2008-01-02 12:00:02.000 1
1 2008-01-02 14:00:00.000 0
1 2008-01-02 17:35:00.000 1
(所影响的行数为 4 行)
delete t
from #tmp t
where exists (
select 1 from #tmp
where empid=a.empid and Fstyle=a.Fstyle
and AttnDate<a.AttnDate
and datediff(mi,AttnDate,a.AttnDate)<5
)
转:潇洒老乌龟(爱新觉罗.毓华)
建议:
比如:
早上上班,在8:00前后一个小时内打卡的有效,其他时间无效.(如果没打卡,算缺勤,9:00以后打卡无效,其他类似)
中午下班,在12:00前后一个小时内打卡的有效,其他时间无效.
下午上班,在13:30前后一个小时内打卡的有效,其他时间无效.
.....
这样就应该行了.
我接触过一些港资企业就是这么干的.
如:7:30-8:05(可以迟到5分钟)打上午的上班卡,其他时间无效,没打的算缺勤.
其他类似.
create table tb(empid int ,AttnDate Datetime, Fstyle Int)
insert into tb
select 1,'2008-01-02 08:59:01',0 ---上班卡多打了一次
union all
select 1,'2008-01-02 09:00:01',0
union all
select 1,'2008-01-02 12:00:02',1
union all
select 1,'2008-01-02 14:00:00',0
union all
select 1,'2008-01-02 17:35:00',1
union all
select 2,'2008-01-02 09:00:00',0
/*
union all
select 2,'2008-01-02 12:00:00',1 ---打卡少打了中间两次
union all
select 2,'2008-01-02 14:00:00',0
*/
union all
select 2,'2008-01-02 17:35:00',1
union all
select 3,'2008-01-02 09:00:00',0
union all
select 3,'2008-01-02 12:00:00',1
union all
select 3,'2008-01-02 14:00:00',0
select empid , 上班时间1 上班时间 , 下班时间1 下班时间 , 上班时间2 上班时间1 , 下班时间2 下班时间1,
cast((case when 下班时间1 is null then 0 else datediff(mi , 上班时间1 , 下班时间1) end + case when 下班时间2 is null then 0 else datediff(mi , 上班时间2 , 下班时间2) end)/30*1.0/2 as decimal(18,1)) 上班总时间 from
(
select empid ,
max(case px when 1 then attndate end) 上班时间1,
max(case px when 2 then attndate end) 下班时间1,
max(case px when 3 then attndate end) 上班时间2,
max(case px when 4 then attndate end) 下班时间2
from
(
select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
) m
group by empid , convert(varchar(10),attndate,120)
) n
drop table tb
/*
empid 上班时间 下班时间 上班时间1 下班时间1 上班总时间
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ --------------------
1 2008-01-02 08:59:01.000 2008-01-02 09:00:01.000 2008-01-02 12:00:02.000 2008-01-02 14:00:00.000 2.0
2 2008-01-02 09:00:00.000 2008-01-02 17:35:00.000 NULL NULL 8.5
3 2008-01-02 09:00:00.000 2008-01-02 12:00:00.000 2008-01-02 14:00:00.000 NULL 3.0
*/
--搞定.
create table tb(empid int ,AttnDate Datetime, Fstyle Int)
insert into tb
select 1,'2008-01-02 09:00:01',0
union all
select 1,'2008-01-02 12:00:02',1
union all
select 1,'2008-01-02 14:00:00',0
union all
select 1,'2008-01-02 17:35:00',1
union all
select 2,'2008-01-02 09:00:00',0
union all
select 2,'2008-01-02 12:00:00',1
union all
select 2,'2008-01-02 14:00:00',0
union all
select 2,'2008-01-02 17:35:00',1
union all
select 3,'2008-01-02 09:00:00',0
union all
select 3,'2008-01-02 12:00:00',1
union all
select 3,'2008-01-02 14:00:00',0
select empid , 上班时间1 上班时间 , 下班时间1 下班时间 , 上班时间2 上班时间 , 下班时间2 下班时间,
cast((case when 下班时间1 is null then 0 else datediff(mi , 上班时间1 , 下班时间1) end + case when 下班时间2 is null then 0 else datediff(mi , 上班时间2 , 下班时间2) end)/30*1.0/2 as decimal(18,1)) 上班总时间 from
(
select empid ,
max(case px when 1 then attndate end) 上班时间1,
max(case px when 2 then attndate end) 下班时间1,
max(case px when 3 then attndate end) 上班时间2,
max(case px when 4 then attndate end) 下班时间2
from
(
select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
) m
group by empid , convert(varchar(10),attndate,120)
) n
drop table tb
/*
empid 上班时间 下班时间 上班时间 下班时间 上班总时间
----- ----------------------- ----------------------- ----------------------------------------------- ----------
1 2008-01-02 09:00:01.000 2008-01-02 12:00:02.000 2008-01-02 14:00:00.000 2008-01-02 17:35:00.000 6.5
2 2008-01-02 09:00:00.000 2008-01-02 12:00:00.000 2008-01-02 14:00:00.000 2008-01-02 17:35:00.000 6.5
3 2008-01-02 09:00:00.000 2008-01-02 12:00:00.000 2008-01-02 14:00:00.000 NULL 3.0
(所影响的行数为 3 行)
*/
create table tb(empid int ,AttnDate Datetime, Fstyle Int)
insert into tb
select 1,'2008-01-02 09:00:01',0
union all
select 1,'2008-01-02 12:00:02',1
union all
select 1,'2008-01-02 14:00:00',0
union all
select 1,'2008-01-02 17:35:00',1
union all
select 2,'2008-01-02 09:00:00',0
union all
select 2,'2008-01-02 12:00:00',1
union all
select 2,'2008-01-02 14:00:00',0
union all
select 2,'2008-01-02 17:35:00',1
union all
select 3,'2008-01-02 09:00:00',0
union all
select 3,'2008-01-02 12:00:00',1
union all
select 3,'2008-01-02 14:00:00',0
select empid , 上班时间1 上班时间 , 下班时间1 下班时间 , 上班时间2 上班时间1 , 下班时间2 下班时间1,
cast((case when 下班时间1 is null then 0 else datediff(mi , 上班时间1 , 下班时间1) end + case when 下班时间2 is null then 0 else datediff(mi , 上班时间2 , 下班时间2) end)/30*1.0/2 as decimal(18,1)) 上班总时间 from
(
select empid ,
max(case px when 1 then attndate end) 上班时间1,
max(case px when 2 then attndate end) 下班时间1,
max(case px when 3 then attndate end) 上班时间2,
max(case px when 4 then attndate end) 下班时间2
from
(
select * , px = (select count(1) from tb where empid = t.empid and convert(varchar(10),attndate,120) = convert(varchar(10),t.attndate,120) and attndate < t.attndate) + 1 from tb t
) m
group by empid , convert(varchar(10),attndate,120)
) n
drop table tb
/*
empid 上班时间 下班时间 上班时间1 下班时间1 上班总时间
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ --------------------
1 2008-01-02 09:00:01.000 2008-01-02 12:00:02.000 2008-01-02 14:00:00.000 2008-01-02 17:35:00.000 6.5
2 2008-01-02 09:00:00.000 2008-01-02 12:00:00.000 2008-01-02 14:00:00.000 2008-01-02 17:35:00.000 6.5
3 2008-01-02 09:00:00.000 2008-01-02 12:00:00.000 2008-01-02 14:00:00.000 NULL 3.0
(所影响的行数为 3 行)
*/