表 table
username(char) intime(数据类型datetime) outtime(数据类型datetime)
A 2006-1-2 08:00:00 2006-1-2 13:13:00
B 2006-1-2 08:11:09 2006-1-2 17:30:00
A 2006-1-3 08:00:00 2006-1-3 17:30:00
A 2006-1-4 08:11:06 2006-1-4 17:13:00
A 2006-1-5 08:02:01 2006-1-5 16:13:00
A 2006-1-6 08:18:00 2006-1-6 15:12:00
B 2006-1-6 08:02:00 2006-1-6 13:10:00
B 2006-1-7 08:05:00 2006-1-7 16:13:00
B 2006-1-8 08:01:00 2006-1-7 12:13:00公司的考勤时间是 8:00 - 17:30,月底做一次统计,
求没位员工 在这个月里 打卡的次数(每日8:00-17:30 只能打一次卡,即记录是唯一)。谢谢各位!
username(char) intime(数据类型datetime) outtime(数据类型datetime)
A 2006-1-2 08:00:00 2006-1-2 13:13:00
B 2006-1-2 08:11:09 2006-1-2 17:30:00
A 2006-1-3 08:00:00 2006-1-3 17:30:00
A 2006-1-4 08:11:06 2006-1-4 17:13:00
A 2006-1-5 08:02:01 2006-1-5 16:13:00
A 2006-1-6 08:18:00 2006-1-6 15:12:00
B 2006-1-6 08:02:00 2006-1-6 13:10:00
B 2006-1-7 08:05:00 2006-1-7 16:13:00
B 2006-1-8 08:01:00 2006-1-7 12:13:00公司的考勤时间是 8:00 - 17:30,月底做一次统计,
求没位员工 在这个月里 打卡的次数(每日8:00-17:30 只能打一次卡,即记录是唯一)。谢谢各位!
declare @t table(username char(10),intime datetime,outtime datetime)
insert into @t select 'A','2006-1-2 08:00:00','2006-1-2 13:13:00'
union all select 'B','2006-1-2 08:11:09','2006-1-2 17:30:00'
union all select 'A','2006-1-3 08:00:00','2006-1-3 17:30:00'
union all select 'A','2006-1-4 08:11:06','2006-1-4 17:13:00'
union all select 'A','2006-1-5 08:02:01','2006-1-5 16:13:00'
union all select 'A','2006-1-6 08:18:00','2006-1-6 15:12:00'
union all select 'B','2006-1-6 08:02:00','2006-1-6 13:10:00'
union all select 'B','2006-1-7 08:05:00','2006-1-7 16:13:00'
union all select 'B','2006-1-8 08:01:00','2006-1-7 12:13:00'
union all select 'B','2006-2-8 08:01:00','2006-2-9 12:13:00'
union all select 'B','2006-2-8 08:01:00','2006-2-9 12:13:00'
union all select 'B','2006-3-8 08:01:00','2006-3-9 12:13:00'
union all select 'B','2006-3-8 08:01:00','2006-3-9 12:13:00'
union all select 'B','2006-3-8 08:01:00','2006-3-9 12:13:00'
union all select 'A','2006-3-8 08:01:00','2006-3-9 12:13:00'select username,
打卡次数=sum(case when month(intime)=month(getdate()) then 1 else 0 end)
from @t group by username
建立测试环境
*/
create table #table(username char(1),intime datetime,outtime datetime)
insert #table
select 'A','2006-1-2 08:00:00','2006-1-2 13:13:00'
union all
select 'B','2006-1-2 08:11:09','2006-1-2 17:30:00'
union all
select 'A','2006-1-3 08:00:00','2006-1-3 17:30:00'
union all
select 'A','2006-1-4 08:11:06','2006-1-4 17:13:00'
union all
select 'A','2006-1-5 08:02:01','2006-1-5 16:13:00'
union all
select 'A','2006-1-6 08:18:00','2006-1-6 15:12:00'
union all
select 'B','2006-1-6 08:02:00','2006-1-6 13:10:00'
union all
select 'B','2006-1-7 08:05:00','2006-1-7 16:13:00'
union all
select 'B','2006-1-8 08:01:00','2006-1-7 12:13:00'declare @year int,@month int,@date datetime,@lastday datetime--最后一天
select @year=2006,@month=1
set @date=convert(datetime,convert(varchar,@year)+'/'+convert(varchar,@month)+'/01')
set @lastday=dateadd(day,-1,convert(char(8),dateadd(month,1,@date),120)+'1')--如果不需要在结果中显示年月,则直接写
/*
select username,count(1)[2006年1月出勤次数] from #table
where intime>= @date and outtime<=@lastday
group by username
*/
declare @sql nvarchar(4000)
set @sql='
select username,count(1)['+convert(varchar,@year)+'年'+convert(varchar,@month)+'月出勤次数] from #table
where intime>=convert(datetime,'''+convert(varchar,@date,120)+''')
and outtime<=convert(datetime,'''+convert(varchar,@lastday,120)+''')
group by username'print (@sql)
drop table #table
/*
result:
(9 row(s) affected)username 2006年1月出勤次数
-------- -----------
A 5
B 4
*/
insert into @t select 'A','2006-1-2 08:00:00','2006-1-2 13:13:00'
union all select 'B','2006-1-2 08:11:09','2006-1-2 17:30:00'
union all select 'A','2006-1-3 08:00:00','2006-1-3 17:30:00'
union all select 'A','2006-1-4 08:11:06','2006-1-4 17:13:00'
union all select 'A','2006-1-5 08:02:01','2006-1-5 16:13:00'
union all select 'A','2006-1-6 08:18:00','2006-1-6 15:12:00'
union all select 'B','2006-1-6 08:02:00','2006-1-6 13:10:00'
union all select 'B','2006-1-7 08:05:00','2006-1-7 16:13:00'
union all select 'B','2006-1-8 08:01:00','2006-1-7 12:13:00'
union all select 'B','2006-2-8 08:01:00','2006-2-9 12:13:00'
union all select 'B','2006-2-8 08:01:00','2006-2-9 12:13:00'
union all select 'B','2006-3-8 08:01:00','2006-3-9 12:13:00'
union all select 'B','2006-3-8 08:01:00','2006-3-9 12:13:00'
union all select 'B','2006-3-8 08:01:00','2006-3-9 12:13:00'
union all select 'A','2006-3-8 08:01:00','2006-3-9 12:13:00'--指定年月参数
declare @yearmonth char(6)
set @yearmonth = '200603'
select username,
打卡次数=count(*)
from @t
where convert(char(6),intime,112)=@yearmonth
group by username
/*username 2006年3月出勤次数
username 打卡次数
A 1
B 3
*/
declare @t table(username varchar(20) , intime datetime , outtime datetime)insert into @t
select 'A' , '2006-3-2 08:00:00' , '2006-3-2 13:13:00' union
select 'B' , '2006-3-2 08:11:09' , '2006-3-2 17:30:00' union
select 'A' , '2006-3-3 08:00:00' , '2006-3-3 17:30:00' union
select 'A' , '2006-3-4 08:11:06' , '2006-3-4 17:13:00' union
select 'A' , '2006-3-5 08:02:01' , '2006-3-5 16:13:00' union
select 'A' , '2006-3-6 08:18:00' , '2006-3-6 15:12:00' union
select 'B' , '2006-3-6 08:02:00' , '2006-3-6 13:10:00' union
select 'B' , '2006-3-7 08:05:00' , '2006-3-7 16:13:00' union
select 'B' , '2006-3-8 08:01:00' , '2006-3-7 12:13:00'select * from @tselect username , count(*) as stat from @t
where convert(char(6),intime,112) = convert(char(6),getdate(),112)
and convert(char(6),outtime,112) = convert(char(6),getdate(),112)
and intime < outtime
and datepart(hh,intime) >= 8
and datepart(hh,outtime) >= 17
and datepart(n,outtime) >= 30
group by username
declare @temp2 table(username varchar(20) ,intime datetime , outtime datetime,num char(8) )insert into @temp1
select 'A' , '2006-1-2 08:00:00','2006-1-2 13:13:00' union all
select 'B' , '2006-1-2 08:11:09','2006-1-2 17:30:00' union all
select 'A' , '2006-1-3 08:00:00','2006-1-3 17:30:00' union all
select 'A' , '2006-1-4 08:11:06','2006-1-4 17:13:00' union all
select 'A' , '2006-1-5 08:02:01','2006-1-5 16:13:00' union all
select 'A' , '2006-1-6 08:18:00','2006-1-6 15:12:00' union all
select 'B' , '2006-1-6 08:02:00','2006-1-6 13:10:00' union all
select 'B' , '2006-1-7 08:05:00','2006-1-7 16:13:00' union all
select 'B' , '2006-1-8 08:01:00','2006-1-7 12:13:00'
insert into @temp2
select username,intime,outtime,convert(char(8),intime,112)
from @temp1
where datepart(hh,intime) >= 8
and datepart(hh,outtime) >= 8
and datepart(hh,outtime) <= 17
and datepart(hh,intime) <= 17
and datepart(n,outtime) <= 30 select username,count(convert(char(8),intime,112))
from @temp2
group by username