/*
我这里没有多的数据,楼主可测试一下。
*/
--辅助函数
create function fnt_getDts(@tdt1 datetime,@tdt2 datetime)
returns @re table(dt datetime)
as
/*
功能:返回两个日期之间出去双休日的连续日期的结果集
说明:如要增加其他公休日期,可调整此处代码
*/
begin
while @tdt1<=@tdt2
begin
if datepart(dw,@tdt1)<>1 and datepart(dw,@tdt1)<>7
insert into @re
select @tdt1
select @tdt1=dateadd(d,1,@tdt1)
end
return
end--传入参数
declare @dt1 varchar(20),@dt2 varchar(20)
select @dt1='08:00:00'
select @dt2='17:00:00'
declare @tdt1 datetime,@tdt2 datetime
select @tdt1='2005-8-15'
select @tdt2='2005-8-17'
--取结果的语句
--合理的日期内只打卡一次当作迟到,如要当作迟到,适当更改dt1和dt2列的值的计算。
select 人员编号=a.personid,名字=a.rname,部门名称=a.department,迟到次数=sum(isnull(b.dt1,1)),早退次数=sum(isnull(b.dt2,1))
from
(select a.personid,a.rname,a.department,b.dt
from Person a,dbo.fnt_getDts(@tdt1,@tdt2) b) a left join
(select personid,dt=convert(varchar(10),inouttime,120),
dt1=case when max(convert(varchar,inouttime,108))=min(convert(varchar,inouttime,108)) then
(case when max(convert(varchar,inouttime,108))>=@dt1 and max(convert(varchar,inouttime,108))<=@dt2
then 1
else
case when max(convert(varchar,inouttime,108))>@dt1 then 1
else 0
end
end) --只打卡一次,按照迟到算
else
(case when min(convert(varchar,inouttime,108))>@dt1 then 1
else 0
end)
end,
dt2=case when max(convert(varchar,inouttime,108))=min(convert(varchar,inouttime,108)) then
(case when max(convert(varchar,inouttime,108))>=@dt2 then 0
else 1
end)
else
(case when max(convert(varchar,inouttime,108))>=@dt2 then 0
else 1
end)
end
from inout
where convert(varchar(10),inouttime,120) between @tdt1 and @tdt2
group by personid,convert(varchar(10),inouttime,120) ) b
on a.personid=b.personid and convert(varchar(10),a.dt,120)=b.dt
group by a.personid,a.rname,a.department
--需要怎样的排序,可自己增加。
/**********结果
人员编号 名字 部门名称 迟到次数 早退次数
----------------------------------------------------------------------
A001 1号 1部门 1 1
A002 2号 1部门 1 1
A003 3号 2部门 2 2*/
我这里没有多的数据,楼主可测试一下。
*/
--辅助函数
create function fnt_getDts(@tdt1 datetime,@tdt2 datetime)
returns @re table(dt datetime)
as
/*
功能:返回两个日期之间出去双休日的连续日期的结果集
说明:如要增加其他公休日期,可调整此处代码
*/
begin
while @tdt1<=@tdt2
begin
if datepart(dw,@tdt1)<>1 and datepart(dw,@tdt1)<>7
insert into @re
select @tdt1
select @tdt1=dateadd(d,1,@tdt1)
end
return
end--传入参数
declare @dt1 varchar(20),@dt2 varchar(20)
select @dt1='08:00:00'
select @dt2='17:00:00'
declare @tdt1 datetime,@tdt2 datetime
select @tdt1='2005-8-15'
select @tdt2='2005-8-17'
--取结果的语句
--合理的日期内只打卡一次当作迟到,如要当作迟到,适当更改dt1和dt2列的值的计算。
select 人员编号=a.personid,名字=a.rname,部门名称=a.department,迟到次数=sum(isnull(b.dt1,1)),早退次数=sum(isnull(b.dt2,1))
from
(select a.personid,a.rname,a.department,b.dt
from Person a,dbo.fnt_getDts(@tdt1,@tdt2) b) a left join
(select personid,dt=convert(varchar(10),inouttime,120),
dt1=case when max(convert(varchar,inouttime,108))=min(convert(varchar,inouttime,108)) then
(case when max(convert(varchar,inouttime,108))>=@dt1 and max(convert(varchar,inouttime,108))<=@dt2
then 1
else
case when max(convert(varchar,inouttime,108))>@dt1 then 1
else 0
end
end) --只打卡一次,按照迟到算
else
(case when min(convert(varchar,inouttime,108))>@dt1 then 1
else 0
end)
end,
dt2=case when max(convert(varchar,inouttime,108))=min(convert(varchar,inouttime,108)) then
(case when max(convert(varchar,inouttime,108))>=@dt2 then 0
else 1
end)
else
(case when max(convert(varchar,inouttime,108))>=@dt2 then 0
else 1
end)
end
from inout
where convert(varchar(10),inouttime,120) between @tdt1 and @tdt2
group by personid,convert(varchar(10),inouttime,120) ) b
on a.personid=b.personid and convert(varchar(10),a.dt,120)=b.dt
group by a.personid,a.rname,a.department
--需要怎样的排序,可自己增加。
/**********结果
人员编号 名字 部门名称 迟到次数 早退次数
----------------------------------------------------------------------
A001 1号 1部门 1 1
A002 2号 1部门 1 1
A003 3号 2部门 2 2*/
--》
--合理的日期内只打卡一次当作迟到,如要当作*早退*,适当更改dt1和dt2列的值的计算。
竟然搞了一个多小时。哎。^_^
select 'A001','2005-8-15 6:00:00' union
select 'A001','2005-8-15 17:00:00' union
select 'A002','2005-8-15 8:00:00' union
select 'A002','2005-8-15 19:00:00' union
select 'A003','2005-8-15 7:30:00' union
select 'A001','2005-8-16 6:00:00' union
select 'A001','2005-8-16 17:00:00' union
select 'A002','2005-8-16 8:00:00' union
select 'A002','2005-8-16 19:00:00' union
select 'A003','2005-8-16 17:30:00'
(
Personid varchar(10),
rname varchar(10),
Department varchar(10)
)
create table inout
(
id bigint IDENTITY (1, 1) NOT NULL,
Personid varchar(10),
inouttime datetime
)
insert Person
select 'A001','1号','1部门' union
select 'A002','2号','1部门' union
select 'A003','3号','2部门'insert inout
select 'A001','2005-8-15 6:00:00' union
select 'A001','2005-8-15 7:00:00' union
select 'A002','2005-8-15 8:00:00' union
select 'A002','2005-8-15 9:00:00' union
select 'A003','2005-8-15 7:30:00' union
select 'A001','2005-8-16 6:00:00' union
select 'A001','2005-8-16 7:00:00' union
select 'A002','2005-8-16 8:00:00' union
select 'A002','2005-8-16 9:00:00' union
select 'A003','2005-8-16 7:30:00'select * from Person
select * from inout
--测试
select P.Personid,
P.rname,
P.Department,
[迟到次数]=sum(case when convert(varchar,A.inouttime,108)>'08:00:00' then 1 else 0 end),
[早退次数]=sum(case when convert(varchar,B.inouttime,108)<'05:00:00' then 1 else 0 end)
from Person P
left join
(
select * from inout t
where
not exists(select 1 from inout where Personid=t.Personid and convert(varchar(10),inouttime,120)=convert(varchar(10),t.inouttime,120) and inouttime<t.inouttime)
)A on P.Personid=A.Personid
left join
(
select * from inout t
where
not exists(select 1 from inout where Personid=t.Personid and convert(varchar(10),inouttime,120)=convert(varchar(10),t.inouttime,120) and inouttime>t.inouttime)
)B on P.Personid=B.Personid
group by P.Personid,P.rname,P.Department--删除测试环境
drop table Person,inout--结果
/*
Personid rname Department 迟到次数 早退次数
---------- ---------- ---------- ----------- -----------
A001 1号 1部门 0 0
A002 2号 1部门 0 0
A003 3号 2部门 0 0(所影响的行数为 3 行)
*/