22,209
社区成员
发帖
与我相关
我的任务
分享
create table #table
(
begin_time datetime,
end_time datetime,
status bit
)
insert #table
select '2012/10/20', '2012/10/23', 1 union all
select '2012/9/24', '2012/10/24', 0 union all
select '2012/10/10', '2012/10/15', 1 union all
select '2012/10/10', '2012/10/15', null union all
select '2012/10/20', '2012/10/23', 0 union all
select '2012/10/20', '2012/10/23', 0 union all
select '2012/10/20', '2012/10/23', 1 union all
select '2012/9/24', '2012/10/24', 0 union all
select '2012/9/24', '2012/10/24', 1
select begin_time, end_time ,
sum(case when status=1 then 1 else 0 end) normal_cnt,
sum(case when status=0 then 1 else 0 end) postpone_cnt,
sum(case when status IS null then 1 else 0 end) unsumbited
from #table group by begin_time, end_time
begin_time end_time normal_cnt postpone_cnt unsumbited
----------------------- ----------------------- ----------- ------------ -----------
2012-10-10 00:00:00.000 2012-10-15 00:00:00.000 1 0 1
2012-10-20 00:00:00.000 2012-10-23 00:00:00.000 2 2 0
2012-09-24 00:00:00.000 2012-10-24 00:00:00.000 1 2 0
(3 row(s) affected)
select 开始时间,结束时间,sum(case when 提交状态='正常' then 1 else 0 end ) '正常数',
sum(case when 提交状态='延迟' then 1 else 0 end ) '延迟数',
sum(case when 提交状态='未提交' then 1 else 0 end ) '未提交数'
from report
group by 开始时间,结束时间