1.在数据库处理统计数据 刚开始是在数据库处理的,后来发现在业务层会更好些 不过还是放这以后用的着
--查询单个员工工作效率
if exists(select * from sysobjects where name='sp_SelectEfficiency')
drop proc sp_SelectEfficiency
go
create proc sp_SelectEfficiency
@CpNumber varchar(20),
@LastTime varchar(20),
@LastTime2 varchar(20)
as
begin
select @LastTime= convert(datetime,(convert(varchar,@LastTime)+' 00:00:00'))
select @LastTime2= convert(datetime,(convert(varchar,@LastTime2)+' 23:59:59'))
declare @table table
(
CName varchar(20),--来电姓名
CountTime int, --来电次数
Time2 float, --在场时间
Time1 float , --在途时间
Efficiency float --工作效率
)
declare @xunhuan int
select @xunhuan=count(*) from tblCheckedPersonInfo
where 1=1
and (CpNumber=@CpNumber or (@CpNumber is null) or len(@CpNumber)=0)
and(LastTime between @LastTime and @LastTime2)
-- group by CpNumber
while(@xunhuan>0)
begin
declare @name varchar(20) --员工姓名
declare @Number varchar(20) --根据不同的 @xunhuan 找出其员工工号
declare @count float --在场时间 小时数
declare @c int --来电次数
declare @countN int --在场时间 分钟数
declare @countN2 float --工作时间
declare @Efficiency float --工作效率
declare @int int
set @int=1
--根据不同的@xunhuan 找出对应的姓名,工号
select @name=CpName,@Number=CpNumber
from (
select CpID,CpName,CpNumber,
(select count(*) from tblCheckedPersonInfo as e2
where e1.CpID >= e2.CpID
and (e2.CpNumber=@CpNumber or (@CpNumber is null) or len(@CpNumber)=0)
and(e2.LastTime between @LastTime and @LastTime2)
) as rownumber
from tblCheckedPersonInfo as e1
where 1=1
and (e1.CpNumber=@CpNumber or (@CpNumber is null) or len(@CpNumber)=0)
and(e1.LastTime between @LastTime and @LastTime2)
) as a
where rownumber=@xunhuan
--首先判断这条信息是否完整 即 要有离开宾馆时间 到达工厂时间 离开工厂时间
--根据工厂代码分组 查询在场时间和
select @countN=sum(a.shijian) from
(
select FacNumber,datediff(n,min(LastTime),max(LastTime)) as shijian from tblCheckedPersonInfo
where 1=1