表一：
员工姓名 员工工号 刷卡时间
蔡燕 20513 2014-6-27 18:20:45
蔡燕 20513 2014-6-27 18:20:47
蔡燕 20513 2014-6-30 8:14:54
蔡燕 20513 2014-7-1 8:17:51
蔡燕 20513 2014-7-1 18:10:50
蔡燕 20513 2014-7-2 8:16:55
蔡燕 20513 2014-7-2 20:09:44
蔡燕 20513 2014-7-3 8:25:18
蔡燕 20513 2014-7-3 18:04:02
蔡燕 20513 2014-7-3 18:04:48
蔡燕 20513 2014-7-3 18:04:50
艾云东 11304 2014-06-23 08:04:18
艾云东 11304 2014-06-23 16:40:50
艾云东 11304 2014-06-24 08:06:47
艾云东 11304 2014-06-24 10:04:23
艾云东 11304 2014-06-24 20:40:39
艾云东 11304 2014-06-25 08:03:31
艾云东 11304 2014-06-25 20:40:19
超过9个小时算加班，我想根据表一计算出某个员工某天是否加班和一个月内的加班次数，得出类似下表：
姓名 日期 是否加班 加班次数
秦燕 2014-6-27 不加班 1
秦燕 2014-6-30 不加班
……
艾云东 2014-06-23 不加班 2
艾云东 2014-06-24 加班
注意：有些是只打上班卡或者只打下班卡不算加班。一天中连续打了多次卡以一天中最早一次卡为上班卡，最晚一次为下班卡
------解决方案--------------------
;
with t(员工姓名 ,员工工号 ,刷卡时间,rid)
as
(select *,convert(date,[刷卡时间]) from [ttt]
)
,tt(员工姓名 ,员工工号,b,rid)
as
(
select 员工姓名 ,员工工号,datediff(hh,min(刷卡时间),max(刷卡时间)) as b,rid from t
group by 员工姓名,员工工号,rid
)
select tt.员工姓名 ,tt.员工工号,tt.rid,case when b>=9 then '加班' else '不加班' end ,c from tt
left join
(select 员工工号,C = sum(case when b>=9 then 1 else 0 end) from tt group by 员工姓名,员工工号) ttt
on tt.员工工号 = ttt.员工工号
------解决方案--------------------
use tempdb
if object_id('test') is not null
drop table test
go
create table test(staff_name varchar(50),staff_id varchar(30),card_time smalldatetime)
insert into test
select '蔡燕', '20513', '2014-6-27 18:20:45' union all
select '蔡燕', '20513', '2014-6-27 18:20:47' union all
select '蔡燕', '20513', '2014-6-30 8:14:54' union all
select '蔡燕', '20513', '2014-7-1 8:17:51' union all
select '蔡燕', '20513', '2014-7-1 18:10:50' union all
select '蔡燕', '20513', '2014-7-2 8:16:55' union all
select '蔡燕', '20513', '2014-7-2 20:09:44' union all
select '蔡燕', '20513', '2014-7-3 8:25:18' union all
select '蔡燕', '20513', '2014-7-3 18:04:02' union all
select '蔡燕', '20513', '2014-7-3 18:04:48' union all
select '蔡燕', '20513', '2014-7-3 18:04:50' union all
select '艾云东', '11304', '2014-06-23 08:04:18' union all
select '艾云东', '11304', '2014-06-23 16:40:50' union all
select '艾云东', '11304', '2014-06-24 08:06:47' union all
select '艾云东', '11304', '2014-06-24 10:04:23' union all
select '艾云东', '11304', '2014-06-24 20:40:39' union all
select '艾云东', '11304', '2014-06-25 08:03:31' union all
select '艾云东', '11304', '2014-06-25 20:40:19'
;with sel as(
select distinct staff_name,staff_id,cast(card_time as date) card_date from test
)
,sel2 as(
select a.staff_name,a.staff_id,a.card_date,case when ( select datediff(hour,min(card_time),max(card_time))
from test where staff_id=a.staff_id and cast(card_time as date)=a.card_date)>9 then '加班' else '不加班' end
as 加班否 from sel a
)
select staff_name,staff_id,t.card_date,加班否, 加班次数=(select count(1) from sel2 where staff_name=t.staff_name and
staff_id=t.staff_id and 加班否='加班' )
from sel2 t
------解决方案--------------------
if object_id('test') is not null
drop table test
go
create table test(staff_name varchar(50),staff_id varchar(30),card_time smalldatetime)
insert into test
select '蔡燕', '20513', '2014-6-27 18:20:45' union all
select '蔡燕', '20513', '2014-6-27 18:20:47' union all
select '蔡燕', '20513', '2014-6-30 8:14:54' union all
select '蔡燕', '20513', '2014-7-1 8:17:51' union all
select '蔡燕', '20513', '2014-7-1 18:10:50' union all
select '蔡燕', '20513', '2014-7-2 8:16:55' union all
select '蔡燕', '20513', '2014-7-2 20:09:44' union all
select '蔡燕', '20513', '2014-7-3 8:25:18' union all
select '蔡燕', '20513', '2014-7-3 18:04:02' union all
select '蔡燕', '20513', '2014-7-3 18:04:48' union all
select '蔡燕', '20513', '2014-7-3 18:04:50' union all
select '艾云东', '11304', '2014-06-23 08:04:18' union all
select '艾云东', '11304', '2014-06-23 16:40:50' union all