时间统计

borwin 2008-03-13 09:01:29
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 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

得出结果如下:

EMP 上班时间 下班时间 上班时间 下班时间 上班总时间
1,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00','2008-01-02 17:35:01' ,7.5
2,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00','2008-01-02 17:35:01' ,7.5
2,'2008-01-02 09:00:00','2008-01-02 12:00:00','2008-01-02 14:00:00', null ,3
...全文
168 点赞 收藏 28
写回复
28 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
star1083 2008-03-15
用得到这个。呵呵
回复
不测试就有错

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
)
回复
borwin 2008-03-14
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

怎样用一条sql语句,将间隔少于5分钟的记录删除,就是将union all
select 1,'2008-01-02 09:00:15',0
union all
select 1,'2008-01-02 09:00:30',0 这两条数据删除了。
回复
borwin 2008-03-13
如果有个表设置了上早的时间段为 09:00:00 到 12:00:00 ,下午段为14:00:00 到 17:00:00,怎样知道有没有早到,迟到,谢谢、。
回复
dawugui 2008-03-13
多余四次的,自己考虑那些数据有效,那些数据无效,把每个人每天整合成四条数据.

这个就不是容易的事情了,详见我上面贴的地址,自己看看.
回复
borwin 2008-03-13
谢谢,还有个问题,如果打卡次数多余四次
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 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 1,'2008-01-02 19:35:00',0
union all
select 1,'2008-01-02 21:35:00',1
如下,有怎样处理,重点是算出总时间
回复
-狙击手- 2008-03-13
死龟
回复
dawugui 2008-03-13
多谢
回复
bqb 2008-03-13
转:潇洒老乌龟(爱新觉罗.毓华) 

建议:
比如:
早上上班,在8:00前后一个小时内打卡的有效,其他时间无效.(如果没打卡,算缺勤,9:00以后打卡无效,其他类似)
中午下班,在12:00前后一个小时内打卡的有效,其他时间无效.
下午上班,在13:30前后一个小时内打卡的有效,其他时间无效.
.....

这样就应该行了.

我接触过一些港资企业就是这么干的.

如:7:30-8:05(可以迟到5分钟)打上午的上班卡,其他时间无效,没打的算缺勤.
其他类似.


严重支持潇洒老乌龟
回复
-狙击手- 2008-03-13
回复
dawugui 2008-03-13
我把那个帖给你看看.

http://topic.csdn.net/u/20080310/09/edbcc51b-ae14-453f-aa47-7d3658eba1e1.html?1516005376

回复
bqb 2008-03-13
计算出勤小时 要根据班次时间 和 打卡时间 来判断!
至于哪个是上班,哪个是下班? 看打卡在哪个时间范围内,如果出现短时间内有多笔数据如(08:59, 09:00)
等可以这样做.在上班卡时间范围内,取最早的一笔,在下班卡时间范围内取最晚一笔

如果条件允许的话,可以采用不同考勤机作为上班,下班打卡,根据机号来判断哪笔是上班卡,哪笔是下班卡!
回复
dawugui 2008-03-13
bqb
----------------------------
个人觉得你考虑复杂了,如果要按你这么算,就那天那个帖,你连哪个是上班,哪个是下班都没法知道.
回复
areswang 2008-03-13
LS说的在理
回复
bqb 2008-03-13
更正:


存在如下问题:
empid 1 多打了一次上班卡 08:59 上班时间由原本6.5小时,变成 2小时
empid 2 漏打了中间两次12:00,14:00 上班时间由原本6.5小时, 变成 8.5小时

楼主有考虑过这些问题吗?员工不可能都规规矩矩 按照你的模式去打卡,计算上班不能这么算?
回复
bqb 2008-03-13
个人觉得这样算有点问题!如下:



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
*/



存在如下问题:
empid 1 多打了一次上班卡 08:59 上班时间由原本6.5小时,变成 2小时
empid 3 漏打了中间两次12:00,14:00 上班时间由原本2小时, 变成 8.5小时

楼主有考虑过这些问题吗?员工不可能都规规矩矩 按照你的模式去打卡,计算上班不能这么算?


回复
dawugui 2008-03-13
结果没对齐,是CSDN排版的问题.
回复
dawugui 2008-03-13

--搞定.
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 行)
*/
回复
dawugui 2008-03-13
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 行)
*/
回复
加载更多回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-13 09:01
社区公告
暂无公告