求一存储过程
declare @t table (taskid,user,pdatetime,fdatetime,startdate,status)
insert into @t select 'T001 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1)
insert into @t select 'T002 ', 'rex ', '2007-6-1 ', NULL, '2007-4-1 ',2)
insert into @t select 'T003 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',0)
insert into @t select 'T004 ', 'sam ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1)
------------
pdatetime:预完成日期
Fdatetime:完成日期
Startdatetime:开始日期
status:任务状态，1为完成，0为进行中，2为未完成
---------
需要得到的结果
用户 完成率 及时率
rex 50% 50%
sam 100% 50%
---------
说明
完成率=完成任务的次数/任务次数（不包括进行中的任务）
及时率 = ((完成任务天数/规定天数)+ (完成任务天数/规定天数)+…)/任务次数（不包含进行中的任务以及未完成的任务）
[解决办法]
declare @t table (taskid varchar(20),[user] varchar(20),pdatetime datetime
,fdatetime datetime,startdate datetime,status int)
insert @t select 'T001 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
insert @t select 'T002 ', 'rex ', '2007-6-1 ', NULL, '2007-4-1 ',2
insert @t select 'T003 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',0
insert @t select 'T004 ', 'sam ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
select distinct [user]
,((select count(1) from @t where [user] = t.[user] and status = 1 )+0.0)
/(select count(1) from @t where [user] = t.[user] and status <> 0) as 完成率
,(select sum((datediff(day,Startdate,Fdatetime)+0.0)/datediff(day,Startdate,pdatetime))
from @t where [user] = t.[user] and status = 1) as 及时率
from @t t
[解决办法]
declare @t table (taskidChar(4),[user] Varchar(10),pdatetime DateTime,fdatetime DateTime,startdate DateTime,status Int)
insert into @t select 'T001 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
insert into @t select 'T002 ', 'rex ', '2007-6-1 ', NULL, '2007-4-1 ',2
insert into @t select 'T003 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',0
insert into @t select 'T004 ', 'sam ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
Select
[user] As 用户,
Rtrim(SUM(Case status When 1 Then 1 Else 0 End) * 100.0 / Count (*)) + '% ' As 完成率,
Rtrim(SUM(DateDiff(dd, startdate, fdatetime) * 100.0 / DateDiff(dd, startdate, pdatetime)) / SUM(Case status When 1 Then 1 Else 0 End) * 100.0) + '% ' As 及时率
From @t
Where status != 0
Group By [user]
[解决办法]
declare @t table(taskid varchar(5),[user] varchar(5),pdatetime datetime,
fdatetime datetime,startdate datetime,status int)
insert into @t select 'T001 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
insert into @t select 'T002 ', 'rex ', '2007-6-1 ', NULL, '2007-4-1 ',2
insert into @t select 'T003 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',0
insert into @t select 'T004 ', 'sam ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
select [user],
[完成率]=sum(case status when 1 then 1 else 0 end)*1.0/sum(case when status in(1,2) then 1 else 0 end),
[及时率]=sum(case status when 1 then datediff(day,startdate,fdatetime)*1.0/datediff(day,startdate,pdatetime) else 0 end)
/sum(case when status in(1,2) then 1 else 0 end)
from @t group by [user]
[解决办法]
declare @t table (taskid varchar(8),[user] varchar(8),pdatetime datetime,
fdatetime datetime,Startdate datetime ,status int)
insert into @t select 'T001 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
insert into @t select 'T002 ', 'rex ', '2007-6-1 ', NULL, '2007-4-1 ',2
insert into @t select 'T003 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',0
insert into @t select 'T004 ', 'sam ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
Select [user],
[完成率]=rtrim(((Select Count(*) from @t where [user]=t.[user] and status=1)*100)/
(Select Count(*) from @t where [user]=t.[user] and status <> 0))+ '% ' ,
[及时率]=rtrim(cast(sum(Case when status=1 then datediff(day,Startdate,Fdatetime)*1.0/
datediff(day,Startdate,pdatetime) else 0 end)/
sum(case when status=1 then 1 else 0 end)*100 as decimal(18,2)))+ '% '
from @t as t group by [user]
[解决办法]
declare @t table (taskidChar(4),[user] Varchar(10),pdatetime DateTime,fdatetime DateTime,startdate DateTime,status Int)
insert into @t select 'T001 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
insert into @t select 'T002 ', 'rex ', '2007-6-1 ', NULL, '2007-4-1 ',2
insert into @t select 'T003 ', 'rex ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',0
insert into @t select 'T004 ', 'sam ', '2007-6-1 ', '2007-5-1 ', '2007-4-1 ',1
Select
[user] As 用户,
Rtrim(Cast((SUM(Case status When 1 Then 1 Else 0 End) * 100.0 / Count (*)) As Decimal(10, 2))) + '% ' As 完成率,
Rtrim(Cast((SUM(DateDiff(dd, startdate, fdatetime) * 100.0 / DateDiff(dd, startdate, pdatetime)) / SUM(Case status When 1 Then 1 Else 0 End)) As Decimal(10, 2))) + '% ' As 及时率
From @t
Where status != 0
Group By [user]
--Result
/*
用户完成率及时率
rex50.00%49.18%
sam100.00%49.18%
*/
[解决办法]
去掉2
不计算未完成,只计算已完成数
