计算每个月的工作小时数。解决方案

计算每个月的工作小时数。
表calendar:
date type
2012-01-01 1 
2012-01-02 1
2012-01-03 1
2012-01-04 1
2012-01-05 1
......
......
2012-12-27 2
2012-12-28 1
2012-12-29 1
2012-12-30 1
2012-12-31 1
 type: 1表示工作日，2表示周末，3表示节假日；
我想要计算，每个月的工作小时数，每天的工作小时数是6.74小时；从上个月的25号到当月的24号算作当月的工作小时数；
结果为：
月份 当月工作小时数
2012-01 148.28
2012-02 132.64
2012-03 147.30
..........

请问这样的sql怎么写？

[解决办法]

SQL code

--> 测试数据：[calendar]if object_id('[calendar]') is not null drop table [calendar]create table [calendar]([date] datetime,[type] int)insert [calendar]select '2012-01-01',1 union allselect '2012-01-02',1 union allselect '2012-01-03',1 union allselect '2012-01-04',2 union allselect '2012-01-05',3 union allselect '2012-12-27',2 union allselect '2012-12-28',1 union allselect '2012-12-29',1 union allselect '2012-12-30',1 union allselect '2012-12-31',3select case when day([date])<=25  then CONVERT(varchar(7),dateadd(mm,-1,[date]),120) else CONVERT(varchar(7),[date],120) end as 月份,SUM(case when [type]=1 then 6.74 else 0 end) as 当月工作小时数from [calendar]group by case when day([date])<=25  then CONVERT(varchar(7),dateadd(mm,-1,[date]),120) else CONVERT(varchar(7),[date],120) end/*月份    当月工作小时数2011-12    20.222012-12    20.22*/反正就是这么一个思路
[解决办法]
--楼上借用一下数据
if object_id('[calendar]') is not null drop table [calendar]
create table [calendar]([date] datetime,[type] int)
insert [calendar]
select '2012-01-01',1 union all
select '2012-01-02',1 union all
select '2012-01-03',1 union all
select '2012-01-04',2 union all
select '2012-01-05',3 union all
select '2012-12-27',2 union all
select '2012-12-28',1 union all
select '2012-12-29',1 union all
select '2012-12-30',1 union all
select '2012-12-31',3


select convert(nvarchar(7),A.date,23),
(select sum(case [type] when 1 then 6.74 else 0 end) from  [calendar] B where convert(nvarchar(7),A.date,23)
=convert(nvarchar(7),[date],23)) 
  from [calendar] A group by convert(nvarchar(7),[date],23)