22,210
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb') is not null drop table tb
go
create table tb(id int identity(1,1),人员卡号 varchar(20), 考勤时间 datetime )
---2
insert tb
select '2038375602',' 2010-03-08 9:25:05' union all
select '2038375602',' 2010-03-08 10:25:05' union all
select '2038375601',' 2010-03-08 10:28:05' union all
select '2038375601',' 2010-03-08 12:28:05' union all
select '2038375601',' 2010-03-08 17:28:05' union all
select '2038375602',' 2010-03-08 12:25:05' union all
select '2038375602',' 2010-03-08 15:25:05' union all
select '2038375603',' 2010-03-08 9:00:00' union all
select '2038375603',' 2010-03-08 10:00:00' union all
select '2038375603',' 2010-03-09 9:00:00' union all
select '2038375603',' 2010-03-09 10:30:00'
alter function f_test(@s datetime,@e datetime)
returns @t table(人员卡号 varchar(50), 考勤时间 datetime ,小时 numeric(4,1))
as
begin
declare @t1 table (rowid int,人员卡号 varchar(50), 考勤时间 datetime )
insert into @t1
select rowid=count(1),a.人员卡号 , a.考勤时间
from tb a left join tb b
on a.人员卡号=b.人员卡号 and a.考勤时间>=b.考勤时间 and datediff(dd,a.考勤时间,b.考勤时间)=0
where a.考勤时间 between @s and @e
group by a.人员卡号 , a.考勤时间
order by a.人员卡号 , a.考勤时间
insert into @t
select 人员卡号=a.人员卡号 + case when max(a.rowid)%2=1 then '该卡刷'+ltrim(max(a.rowid))+'次,请查明问题!'else '' end,
convert(varchar(10),a.考勤时间,120) 考勤时间,
工作时间=sum(case when a.rowid%2=1 then datediff(mi,a.考勤时间,b.考勤时间) else 0 end)*1.0/60
from @t1 a left join @t1 b
on a.人员卡号=b.人员卡号 and a.rowid=b.rowid-1 and datediff(dd,a.考勤时间,b.考勤时间)=0
group by a.人员卡号,convert(varchar(10),a.考勤时间,120)
return
end
select * from dbo.f_test('2010-03-01','2010-03-10')
/*
人员卡号 考勤时间 小时
-------------------------------------------------- ------------------------------------------------------ ------
2038375601该卡刷3次,请查明问题! 2010-03-08 00:00:00.000 2.0
2038375602 2010-03-08 00:00:00.000 4.0
2038375603 2010-03-08 00:00:00.000 1.0
2038375603 2010-03-09 00:00:00.000 1.5
(所影响的行数为 4 行)
*/
---1
if object_id('tb') is not null drop table tb
go
create table tb(id int identity(1,1),人员卡号 varchar(20), 考勤时间 datetime )
---2
insert tb
select '2038375602',' 2010-03-08 9:25:05' union all
select '2038375602',' 2010-03-08 10:25:05' union all
select '2038375601',' 2010-03-08 10:28:05' union all
select '2038375601',' 2010-03-08 12:28:05' union all
select '2038375601',' 2010-03-08 17:28:05' union all
select '2038375602',' 2010-03-08 12:25:05' union all
select '2038375602',' 2010-03-08 15:25:05' union all
select '2038375603',' 2010-03-08 9:00:00' union all
select '2038375603',' 2010-03-08 10:00:00' union all
select '2038375603',' 2010-03-09 9:00:00' union all
select '2038375603',' 2010-03-09 10:30:00'
alter function f_test(@s datetime,@e datetime)
returns @t table(人员卡号 varchar(50), 考勤时间 datetime ,小时 numeric(4,1))
as
begin
declare @t1 table (rowid int,人员卡号 varchar(50), 考勤时间 datetime )
insert into @t1
select rowid=count(1),a.人员卡号 , a.考勤时间
from tb a left join tb b
on a.人员卡号=b.人员卡号 and a.考勤时间>=b.考勤时间 and datediff(dd,a.考勤时间,b.考勤时间)=0
where a.考勤时间 between @s and @e
group by a.人员卡号 , a.考勤时间
order by a.人员卡号 , a.考勤时间
insert into @t
select 人员卡号=a.人员卡号 + case when max(a.rowid)%2=1 then '该卡刷'+ltrim(max(a.rowid))+'次,请查明问题!'else '' end,
convert(varchar(10),a.考勤时间,120) 考勤时间,
工作时间=sum(case when a.rowid%2=0 then datediff(mi,b.考勤时间,a.考勤时间) else datediff(mi,a.考勤时间,b.考勤时间)end)*1.0/60
from @t1 a left join @t1 b
on a.人员卡号=b.人员卡号 and a.rowid=b.rowid+1 and datediff(dd,a.考勤时间,b.考勤时间)=0
group by a.人员卡号,convert(varchar(10),a.考勤时间,120)
return
end
select * from dbo.f_test('2010-03-01','2010-03-10')
/*
人员卡号 考勤时间 小时
-------------------------------------------------- ------------------------------------------------------ ------
2038375601该卡刷3次,请查明问题! 2010-03-08 00:00:00.000 -3.0
2038375602 2010-03-08 00:00:00.000 2.0
2038375603 2010-03-08 00:00:00.000 1.0
2038375603 2010-03-09 00:00:00.000 1.5
(所影响的行数为 4 行)
*/