MSSQL 构建日期范围内的连续月份或日期(不借助系统表的方法)

-- 注; 该方法在MSSQL2005或者以上版本适用

--A 为需要统计的原始数据

--B 构建连续日期使用, 一月最多 31 天, 所以最多需要到30.

--C 找出最小和最大日期

;with A(日期,金额) as

(                             
select '2014-05-01','100' union all
select '2014-05-02','200' union all
select '2014-05-05','300' union all
select '2014-05-06','200'
),
B(id) as 
(
    select 0 union all
    select 1 union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5 union all
    select 6 union all
    select 7 union all
    select 8 union all
    select 9 union all
    select 10 union all
    select 11 union all
    select 12 union all
    select 13 union all
    select 14 union all
    select 15 union all
    select 16 union all
    select 17 union all
    select 18 union all
    select 19 union all    
    select 20 union all
    select 21 union all
    select 22 union all
    select 23 union all
    select 24 union all
    select 25 union all
    select 26 union all
    select 27 union all
    select 28 union all
    select 29 union all
    select 30 
),
C(mindt, maxdt) as
(
    select Min(日期) as mindt,  Max(日期) as maxdt from A
)
select T.日期, ISNULL(A.金额, 0) as 金额 from
(
select convert(varchar(10),dateadd(day, id, (select mindt from C)),120) as 日期
from B
) as T
left join A 
on A.日期=T.日期

where T.日期 <= (select maxdt from C)

执行结果:

日期               金额

2014-05-01 100
2014-05-02 200
2014-05-03 0
2014-05-04 0
2014-05-05 300
2014-05-06 200