求sql统计,该如何解决

求sql统计
有一张加班和调休表，结构如下

id int 自增
type varchar(10) 1为加班，2为调休
counttime decima(5,1) 总时间数(加班或调休的时间数)
startdate datetime 开始时间
enddate datetime 结束时间


数据如下：
type startdate enddate counttime id
12012-04-18 14:33:22.640 2012-04-21 14:33:22.6403.0 1
12012-04-14 14:34:48.970 2012-04-17 14:34:48.9703.0 2
22012-04-19 14:34:49.407 2012-04-21 14:34:49.4072.0 3
32012-04-23 14:34:50.220 2012-04-24 14:34:50.2201.0 4

现我要统计 加班总时数，调休总时数 本月结余数（调休总时数-加班总时数）

sql该怎么写，求高人。。。。

[解决办法]

SQL code

create table tb(  [type] varchar(10),  startdate datetime,  enddate datetime,  counttime decimal(5,1),  id int identity(1,1))insert into tb select '1', '2012-04-18 14:33:22.640', '2012-04-21 14:33:22.640', 3.0 union allselect '1', '2012-04-14 14:34:48.970', '2012-04-17 14:34:48.970', 3.0 union allselect '2', '2012-04-19 14:34:49.407', '2012-04-21 14:34:49.407', 2.0 union allselect '2', '2012-04-23 14:34:50.220', '2012-04-24 14:34:50.220', 1.0select t1.加班时间 ,t2.调休时间 ,t1.加班时间 -t2.调休时间 AS 结余数 from(select [type],SUM(counttime) AS 加班时间 from tb where [type]='1' group by [type]) t1,(select [type],SUM(counttime) AS 调休时间 from tb where [type]='2' group by [type]) t2
[解决办法]
SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([type] int,[startdate] datetime,[enddate] datetime,[counttime] numeric(2,1),[id] int)insert [test]select 1,'2012-04-18 14:33:22.640','2012-04-21 14:33:22.640',3.0,1 union allselect 1,'2012-04-14 14:34:48.970','2012-04-17 14:34:48.970',3.0,2 union allselect 2,'2012-04-19 14:34:49.407','2012-04-21 14:34:49.407',2.0,3 union allselect 2,'2012-04-23 14:34:50.220','2012-04-24 14:34:50.220',1.0,4select 加班总时数,调休总时数,调休总时数-加班总时数 as 本月结余 from(select SUM(case when [type]=1 then [counttime] else 0 end) as 加班总时数,SUM(case when [type]=2 then [counttime] else 0 end) as 调休总时数from test)a/*加班总时数    调休总时数    本月结余6.0    3.0    -3.0*/