补全日期的SQL语句，你会么

[a15152600000]

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最小的，最大的。帮我补全代码好么

[还在加载中灬]

不知道这样可不可行，你在你原表上，加上那些月份的空数据

[a15152600000]

我要的是动态的。这样我也会啊，我估计可能要用union一个日期是所有月的25号，然后再来个between and的。

[还在加载中灬]

我的意思也是这样的 在你搜索结果上，UNION一些所有月份的空数据

;WITH CTE0 AS (SELECT ... FROM ... WHERE DT>=... AND DT<=... UNION ALL '某月到某月的空数据')
,CTE AS(
'上面你写的搜索语句，将原来的表换成CTE0'
)

至于动态效果，即动态添加空数据，这个可以在程序上实现，然后限定查询时间必填

[haitao]

计算2个日期(d1、d2)间隔的天数n 根据序号表生成一个n条记录的记录集 select @d1+a.i fdate from 某个系统表 a where a.i>=0 and a.i<n

[DBA_磊仔]

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 行受影响)*/

[DBA_磊仔]

dbo.nums 是数字辅助表
--简易数字辅助表
select number  from master..spt_values where type='p' and number between 1 and 10
--创建一般的数字辅助表
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 10000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums 
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO

[carychuang]

5楼正解，请查收

[-晴天]

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 行受影响)

*/

[a15152600000]

谢谢大家，知识浅薄，有些还是不懂，结贴了