求一存储过程
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 (taskid Char(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 &#