补全日期的SQL语句，你会么
if object_id('tempdb.dbo.#TA') is not null drop table #TA go
create table #TA([DT] datetime,[Qty] int)
insert #TA select '2009-12-23',5
union allselect '2010-01-20',1
union allselect '2010-01-25',2
union allselect '2010-01-26',3
union allselect '2010-02-27',4
union allselect '2010-03-25',5
union allselect '2010-12-26',100
union allselect '2011-01-23',6
union allselect '2011-01-25',7
union allselect '2011-02-03',8
union allselect '2011-03-03',9
union allselect '2011-03-25',10
union allselect '2011-03-26',11
;WITH CET AS
(
select case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty) SumQty
from #TA
group by case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
)
SELECT O1.YM, SUM(O2.SumQty) AS totalqty
FROM CET AS O1
JOIN CET AS O2
ON O2.YM <= O1.YM and o2.YM>dateadd(month,-12,o1.YM)
GROUP BY O1.YM
ORDER BY O1.YM;
----------------结果----------------------------
/*
YM totalqty
---------- -----------
2010-01-25 5
2010-02-25 8
2010-03-25 11
2010-04-25 20
2011-02-25 125
2011-03-25 130
2011-04-25 140
2011-05-25 151
这里的结果日期如果没出现的能帮我不全么，求的是12个月内的数量和。比如结果应该有2010-05-25也应该有个20，求的是2009-04-26到2010-04-25的数量和。这里中间所有没出现过的日期（每个月的25号）都要补全，相当于上面的日期和一个循环的日期求和，那个表的totalQty为0，日期应该between最小的，最大的。帮我补全代码好么
[解决办法]
计算2个日期(d1、d2)间隔的天数n
根据序号表生成一个n条记录的记录集
select @d1+a.i fdate
from 某个系统表 a
where a.i>=0 and a.i<n
[解决办法]
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([DT] datetime,[Qty] int)
insert #TA select '2009-12-23',5
union all select '2010-01-20',1
union all select '2010-01-25',2
union all select '2010-01-26',3
union all select '2010-02-27',4
union all select '2010-03-25',5
union all select '2010-12-26',100
union all select '2011-01-23',6
union all select '2011-01-25',7
union all select '2011-02-03',8
union all select '2011-03-03',9
union all select '2011-03-25',10
union all select '2011-03-26',11
;WITH CET0 AS
(
SELECT * FROM #TA
UNION ALL
SELECT dateadd(mm,1 - n ,GETDATE()),0--GETDATE() 可以考虑换成 (select max(DT) from #TA)
FROM dbo.Nums
WHERE n <= DATEDIFF(mm, (select min(DT) from #TA), GETDATE())),--GETDATE() 可以考虑换成 (select max(DT) from #TA)
CET AS
(
select case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty) SumQty
from CET0
group by case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
)
SELECT O1.YM, SUM(O2.SumQty) AS totalqty
FROM CET AS O1
JOIN CET AS O2
ON O2.YM <= O1.YM and o2.YM>dateadd(month,-12,o1.YM)
GROUP BY O1.YM
ORDER BY O1.YM;
/*
(13 行受影响)
YM totalqty
---------- -----------
2010-01-25 5
2010-02-25 8
2010-03-25 11
2010-04-25 20
2010-05-25 20
2010-06-25 20
2010-07-25 20
2010-08-25 20
2010-09-25 20
2010-10-25 20
2010-11-25 20
2010-12-25 20
2011-01-25 15
2011-02-25 125
2011-03-25 130
2011-04-25 140
2011-05-25 151
2011-06-25 151
2011-07-25 151
2011-08-25 151
2011-09-25 151
2011-10-25 151
2011-11-25 151
2011-12-25 151
2012-01-25 151
2012-02-25 38
2012-03-25 30
2012-04-25 11
2012-05-25 0
2012-06-25 0
2012-07-25 0
2012-08-25 0
2012-09-25 0
2012-10-25 0
2012-11-25 0
2012-12-25 0
2013-01-25 0
2013-02-25 0
2013-03-25 0
(39 行受影响)*/
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([DT] datetime,[Qty] int)
insert #TA select '2009-12-23',5
union all select '2010-01-20',1
union all select '2010-01-25',2
union all select '2010-01-26',3
union all select '2010-02-27',4
union all select '2010-03-25',5
union all select '2010-12-26',100
union all select '2011-01-23',6
union all select '2011-01-25',7
union all select '2011-02-03',8
union all select '2011-03-03',9
union all select '2011-03-25',10
union all select '2011-03-26',11
;WITH CET AS
(
select case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end as YM, SUM(Qty) SumQty
from #TA
group by case when datepart(DD,dt)>25 then convert(varchar(7), DATEADD(MM,2,dt),120) + '-25'
else convert(varchar(7), DATEADD(MM,1,dt),120) + '-25' end
),c2 as(
SELECT O1.YM, SUM(O2.SumQty) AS totalqty
FROM CET AS O1
JOIN CET AS O2
ON O2.YM <= O1.YM and o2.YM>dateadd(month,-12,o1.YM)
GROUP BY O1.YM
--ORDER BY O1.YM
)
select a.ym,isnull(b.totalqty,0) as totalqty from(
select dateadd(m,b.number,a.ym1)ym
from (select min(ym) as ym1,max(ym) as ym2 from cet) a,master..spt_values b
where b.type='p' and dateadd(m,b.number,a.ym1)<=a.ym2
)a left join c2 b on a.ym=b.ym
/*
ym totalqty
----------------------- -----------
2010-01-25 00:00:00.000 5
2010-02-25 00:00:00.000 8
2010-03-25 00:00:00.000 11
2010-04-25 00:00:00.000 20
2010-05-25 00:00:00.000 0
2010-06-25 00:00:00.000 0
2010-07-25 00:00:00.000 0
2010-08-25 00:00:00.000 0
2010-09-25 00:00:00.000 0
2010-10-25 00:00:00.000 0
2010-11-25 00:00:00.000 0
2010-12-25 00:00:00.000 0
2011-01-25 00:00:00.000 0
2011-02-25 00:00:00.000 125
2011-03-25 00:00:00.000 130
2011-04-25 00:00:00.000 140
2011-05-25 00:00:00.000 151
(17 行受影响)
*/