select 
    [名字],
    count(1)over(partition by [名字]) as 任务数,
    SUM(case when [任务状态]='正常关闭' then 1 else 0 end) as 完成数,
    SUM(case when [任务状态]='异常关闭' or [任务完成日期] is null then 1 else 0 end) as 未完成数,
    SUM(case when 任务状态=[任务完成日期] then 1 else 0 end) as 异常关闭数,
    SUM(case when 任务完成日期>任务截止日期 then 1 else 0 end) as 逾期数,
    ltrim(SUM(case when [任务状态]='正常关闭' then 1 else 0 end)*100.0/count(1)over(partition by [名字]))+'%'as 完成率,
    ltrim(SUM(case when 任务完成日期>任务截止日期 then 1 else 0 end)*100.0/SUM(case when [任务状态]='正常关闭' then 1 else 0 end)) as 逾期率
from
    [Demo]
group by
    [名字]
    

--应该是对的

------解决方案--------------------

行转列。。。 日经帖。

------解决方案--------------------

SQL code
----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-07-18 13:01:26
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[demo]
if object_id('[demo]') is not null drop table [demo]
go 
create table [demo]([id] int,[名字] varchar(6),[任务开始日期] datetime,[任务截至日期] datetime,[任务完成日期] varchar(14),[任务状态] varchar(8))
insert [demo]
select 1,'hbwang','2012-7-11 0:00','2012-7-18 0:00','2012-7-17 0:00','正常关闭' union all
select 2,'alma','2012-7-12 0:00','2012-7-19 0:00','2012-7-18 0:00','正常关闭' union all
select 3,'hbwang','2012-7-13 0:00','2012-7-20 0:00','2012-7-21 0:00','正常关闭' union all
select 4,'alma','2012-7-14 0:00','2012-7-21 0:00','2012-7-21 0:00','正常关闭' union all
select 5,'alma','2012-7-15 0:00','2012-7-22 0:00',null,'异常关闭'union all
select 6,'hbwang','2012-7-16 0:00','2012-7-23 0:00',null,null union all
select 7,'alma','2012-7-17 0:00','2012-7-24 0:00','2012-7-24 0:00','正常关闭' union all
select 8,'jjpan','2012-7-18 0:00','2012-7-25 0:00','2012-7-25 0:00','正常关闭' union all
select 9,'jjpan','2012-7-19 0:00','2012-7-26 0:00','2012-7-27 0:00','正常关闭' union all
select 10,'jjpan','2012-7-20 0:00','2012-7-27 0:00',null,null union all
select 11,'hbwang','2012-7-21 0:00','2012-7-28 0:00','2012-7-27 0:00','正常关闭'
--------------开始查询--------------------------
select 
    [名字],
    count(1) as 任务数,
    sum(case [任务状态] when  '正常关闭' then 1 else 0 end) as 完成数,
    sum(case  when  [任务状态]<>'正常关闭' then 1 else 0 end) as 未完成数,
    sum(case [任务状态] when  '异常关闭' then 1 else 0 end) as 异常关闭数,
    sum(case when [任务完成日期]>[任务截至日期] then 1 else 0 end) as 逾期数,
    ltrim(cast(sum(case when [任务状态]='正常关闭' then 1 else 0 end)*100.0/count(1) as dec(18,2)))+'%'as 完成率,
    ltrim(c