----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-07-18 15:01:02
-- 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)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([id] int,[name] varchar(14),[ordertype] int,[ordertime] datetime)
insert [tb]
select 1,'某公司的预约',0,'2012-5-29' union all
select 2,'某个人的预约',1,'2012-5-30' union all
select 3,'某集团1的预约',0,'2012-5-31' union all
select 4,'某集团2的预约',1,'2012-6-1' union all
select 5,'某集团3的预约',1,'2012-6-2' union all
select 6,'某集团4的预约',1,'2012-6-3' union all
select 7,'某集团5的预约',1,'2012-6-4' union all
select 8,'某集团6的预约',1,'2012-6-5' union all
select 9,'某集团7的预约',0,'2012-6-6' union all
select 10,'某集团8的预约',0,'2012-6-7' union all
select 11,'某集团9的预约',0,'2012-6-8' union all
select 12,'某集团10的预约',2,'2012-6-2' union all
select 13,'某集团11的预约',2,'2012-6-12' union all
select 14,'某集团12的预约',2,'2012-6-12'
--------------开始查询--------------------------

select
  isnull((case when ordertype=2 then '现场预约' when ordertype=1 then '电话预约' when ordertype=0 then '网上预约' else null end) ,'总数') as 预约情况,
  sum(case when datediff(dd,orderTime,getdate())=0 then 1 else 0 end) as 按天,
  sum(case when datediff(mm,orderTime,getdate())=0 then 1 else 0 end) as 按月,
  sum(case when datediff(yy,orderTime,getdate())=0 then 1 else 0 end) as 按年
from
  tb
group by
  isnull((case when ordertype=2 then '现场预约' when ordertype=1 then '电话预约' when ordertype=0 then '网上预约' end) ,'总数')
with rollup

----------------结果----------------------------
/* 预约情况     按天          按月          按年
-------- ----------- ----------- -----------
电话预约     0           0           6
网上预约     0           0           5
现场预约     0           0           3
NULL     0           0           14

(4 行受影响)

*/