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

a15152600000 2013-02-13 11:52:20
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最小的,最大的。帮我补全代码好么
...全文
936 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
a15152600000 2013-06-27
  • 打赏
  • 举报
回复
谢谢大家,知识浅薄,有些还是不懂,结贴了
-晴天 2013-02-23
  • 打赏
  • 举报
回复
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 行受影响)

*/
carychuang 2013-02-23
  • 打赏
  • 举报
回复
5楼正解,请查收
DBA_磊仔 2013-02-18
  • 打赏
  • 举报
回复

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
DBA_磊仔 2013-02-18
  • 打赏
  • 举报
回复
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 行受影响)*/
haitao 2013-02-17
  • 打赏
  • 举报
回复
计算2个日期(d1、d2)间隔的天数n 根据序号表生成一个n条记录的记录集 select @d1+a.i fdate from 某个系统表 a where a.i>=0 and a.i<n
还在加载中灬 2013-02-17
  • 打赏
  • 举报
回复
我的意思也是这样的 在你搜索结果上,UNION一些所有月份的空数据
;WITH CTE0 AS (SELECT ... FROM ... WHERE DT>=... AND DT<=... UNION ALL '某月到某月的空数据')
,CTE AS(
'上面你写的搜索语句,将原来的表换成CTE0'
)
至于动态效果,即动态添加空数据,这个可以在程序上实现,然后限定查询时间必填
a15152600000 2013-02-16
  • 打赏
  • 举报
回复
我要的是动态的。这样我也会啊,我估计可能要用union一个日期是所有月的25号,然后再来个between and的。
还在加载中灬 2013-02-16
  • 打赏
  • 举报
回复
不知道这样可不可行,你在你原表上,加上那些月份的空数据
数据清洗 1.基本概念 数据清洗从名字上也看的出就是把"脏"的"洗掉",指发现并纠正数据文件中可识别 的错误的最后一道程序,包括检查数据一致性,处理无效值和缺失值等。因为数据仓库 中的数据是面向某一主题的数据的集合,这些数据从多个业务系统中抽取而来而且包含 历史数据,这样就避免不了有的数据是错误数据、有的数据相互之间有冲突,这些错误 的或有冲突的数据显然是我们不想要的,称为"脏数据"。我们要按照一定的规则把"脏数 据""洗掉",这就是数据清洗。而数据清洗的任务是过滤那些不符合要求的数据,将过滤 的结果交给业务主管部门,确认是否过滤掉还是由业务单位修正之后再进行抽取。不符 合要求的数据主要是有不完整的数据、错误的数据、重复的数据三大类。数据清洗是与 问卷审核不同,录入后的数据清理一般是由计算机而不是人工完成。 折叠残缺数据 这一类数据主要是一些应该有的信息缺失,如供应商的名称、分公司的名称、客户 的区域信息缺失、业务系统中主表与明细表不能匹配等。对于这一类数据过滤出来,按 缺失的内容分别写入不同Excel文件向客户提交,要求在规定的时间内补全补全后才写 入数据仓库。 折叠错误数据 这一类错误产生的原因是业务系统不够健全,在接收输入后没有进行判断直接写入 后台数据库造成的,比如数值数据输成全角数字字符、字符串数据后面有一个回车操作 、日期格式不正确、日期越界等。这一类数据也要分类,对于类似于全角字符、数据前 后有不可见字符的问题,只能通过写SQL语句的方式找出来,然后要求客户在业务系统修 正之后抽取。日期格式不正确的或者是日期越界的这一类错误导致ETL运行失败,这一 类错误需要去业务系统数据库用SQL的方式挑出来,交给业务主管部门要求限期修正,修 正之后再抽取。 折叠重复数据 对于这一类数据--特别是维表中出现这种情况-- 将重复数据记录的所有字段导出来,让客户确认并整理。 数据清洗是一个反复的过程,不可能在几天内完成,只有不断的发现问题,解决问 题。对于是否过滤,是否修正一般要求客户确认,对于过滤掉的数据,写入Excel文件或 者将过滤数据写入数据表,在ETL开发的初期可以每天向业务单位发送过滤数据的邮件, 促使他们尽快地修正错误,同时也可以做为将来验证数据的依据。数据清洗需要注意的是 不要将有用的数据过滤掉,对于每个过滤规则认真进行验证,并要用户确认。 数据分析是指用适当的统计分析方法对收集来的大量数据进行分析,提取有用信息 和形成结论而对数据加以详细研究和概括总结的过程。这一过程也是质量管理体系的支 持过程。在实用中,数据分析可帮助人们作出判断,以便采取适当行动。 类型 在统计学领域,有些人将数据分析划分为描述性统计分析、探索性数据分析以及验 证性数据分析;其中,探索性数据分析侧重于在数据之中发现新的特征,而验证性数据分 析则侧重于已有假设的证实或证伪。 探索性数据分析是指为了形成值得假设的检验而对数据进行分析的一种方法,是对 传统统计学假设检验手段的补充。该方法由美国著名统计学家约翰·图基(John Tukey)命名。 定性数据分析又称为"定性资料分析"、"定性研究"或者"质性研究资料分析",是指 对诸如词语、照片、观察结果之类的非数值型数据(或者说资料)的分析。 数据挖掘(英语:Data mining),又译为资料探勘、数据采矿。它是数据库知识发现(英语:Knowledge- Discovery in Databases,简称:KDD)中的一个步骤。数据挖掘一般是指从大量的数据中自动搜索隐藏 于其中的有着特殊关系性(属于Association rule learning)的信息的过程。数据挖掘通常与计算机科学有关,并通过统计、在线分析处 理、情报检索、机器学习、专家系统(依靠过去的经验法则)和模式识别等诸多方法来 实现上述目标。 折叠数据挖掘的基本步骤 数据挖掘的步骤随不同领域的应用而有所变化,每一种数据挖掘技术也有各自 的特性和使用步骤,针对不同问题和需求所制定的数据挖掘过程也存在差异。此外, 数据的完整程度、专业人员支持的程度等都对建立数据挖掘过程有所影响。这些因素 造成了数据挖掘在各不同领域中的运用、规划,以及流程的差异性,即使同一产业,也 因为分析技术和专业知识的涉入程度不同而不同,因此对于数据挖掘过程的系统化、 标准化就显得格外重要。如此一来,不仅可以较容易地跨领域应用,也可以结合不同的 专业知识,发挥数据挖掘的真正精神。 数据挖掘完整的步骤如下: 理解数据和数据的来源(understanding)。 获取相关知识与技术(acquisition)。 整合与检查数据(integration and checking)。 去除错误或不一致的数据(data cleaning)。 建立模型和假设(mod
数据清洗数据分析数据挖掘全文共4页,当前为第1页。数据清洗数据分析数据挖掘全文共4页,当前为第1页。数据清洗 数据清洗数据分析数据挖掘全文共4页,当前为第1页。 数据清洗数据分析数据挖掘全文共4页,当前为第1页。 1.基本概念 数据清洗从名字上也看的出就是把"脏"的"洗掉",指发现并纠正数据文件中可识别的错误的最后一道程序,包括检查数据一致性,处理无效值和缺失值等。因为数据仓库中的数据是面向某一主题的数据的集合,这些数据从多个业务系统中抽取而来而且包含历史数据,这样就避免不了有的数据是错误数据、有的数据相互之间有冲突,这些错误的或有冲突的数据显然是我们不想要的,称为"脏数据"。我们要按照一定的规则把"脏数据""洗掉",这就是数据清洗。而数据清洗的任务是过滤那些不符合要求的数据,将过滤的结果交给业务主管部门,确认是否过滤掉还是由业务单位修正之后再进行抽取。不符合要求的数据主要是有不完整的数据、错误的数据、重复的数据三大类。数据清洗是与问卷审核不同,录入后的数据清理一般是由计算机而不是人工完成。 主要类型 残缺数据 这一类数据主要是一些应该有的信息缺失,如供应商的名称、分公司的名称、客户的区域信息缺失、业务系统中主表与明细表不能匹配等。对于这一类数据过滤出来,按缺失的内容分别写入不同Excel文件向客户提交,要求在规定的时间内补全补全后才写入数据仓库。 折叠错误数据 数据清洗数据分析数据挖掘全文共4页,当前为第2页。数据清洗数据分析数据挖掘全文共4页,当前为第2页。这一类错误产生的原因是业务系统不够健全,在接收输入后没有进行判断直接写入后台数据库造成的,比如数值数据输成全角数字字符、字符串数据后面有一个回车操作、日期格式不正确、日期越界等。这一类数据也要分类,对于类似于全角字符、数据前后有不可见字符的问题,只能通过写SQL语句的方式找出来,然后要求客户在业务系统修正之后抽取。日期格式不正确的或者是日期越界的这一类错误导致ETL运行失败,这一类错误需要去业务系统数据库用SQL的方式挑出来,交给业务主管部门要求限期修正,修正之后再抽取。 数据清洗数据分析数据挖掘全文共4页,当前为第2页。 数据清洗数据分析数据挖掘全文共4页,当前为第2页。 折叠重复数据 对于这一类数据--特别是维表中出现这种情况--将重复数据记录的所有字段导出来,让客户确认并整理。 数据清洗是一个反复的过程,不可能在几天内完成,只有不断的发现问题,解决问题。对于是否过滤,是否修正一般要求客户确认,对于过滤掉的数据,写入Excel文件或者将过滤数据写入数据表,在ETL开发的初期可以每天向业务单位发送过滤数据的邮件,促使他们尽快地修正错误,同时也可以做为将来验证数据的依据。数据清洗需要注意的是不要将有用的数据过滤掉,对于每个过滤规则认真进行验证,并要用户确认。 数据分析是指用适当的统计分析方法对收集来的大量数据进行分析,提取有用信息和形成结论而对数据加以详细研究和概括总结的过程。这一过程也是质量管理体系的支持过程。在实用中,数据分析可帮助人们作出判断,以便采取适当行动。 数据清洗数据分析数据挖掘全文共4页,当前为第3页。数据清洗数据分析数据挖掘全文共4页,当前为第3页。类型 数据清洗数据分析数据挖掘全文共4页,当前为第3页。 数据清洗数据分析数据挖掘全文共4页,当前为第3页。 在统计学领域,有些人将数据分析划分为描述性统计分析、探索性数据分析以及验证性数据分析;其中,探索性数据分析侧重于在数据之中发现新的特征,而验证性数据分析则侧重于已有假设的证实或证伪。 探索性数据分析是指为了形成值得假设的检验而对数据进行分析的一种方法,是对传统统计学假设检验手段的补充。该方法由美国着名统计学家约翰·图基(John Tukey)命名。 定性数据分析又称为"定性资料分析"、"定性研究"或者"质性研究资料分析",是指对诸如词语、照片、观察结果之类的非数值型数据(或者说资料)的分析。 数据挖掘(英语:Data mining),又译为资料探勘、数据采矿。它是数据库知识发现(英语:Knowledge-Discovery in Databases,简称:KDD)中的一个步骤。数据挖掘一般是指从大量的数据中自动搜索隐藏于其中的有着特殊关系性(属于Association rule learning)的信息的过程。数据挖掘通常与计算机科学有关,并通过统计、在线分析处理、情报检索、机器学习、专家系统(依靠过去的经验法则)和模式识别等诸多方法来实现上述目标。 数据挖掘的基本步骤 数据挖掘的步骤随不同领域的应用而有所变化,每一种数据挖掘技术也有各自的特性和使用步骤,针对不同问题和需求所制定的数据挖掘过程也存在差异。此外,数据的完整程度、专业人员支持的程度等都对建立数据挖掘过程有所影响。这些因素造

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧