如何查找遗漏日期

Question

我这里有个很大的表，按照数据顺序排列如下20010101 
20010402 
20010523 
20010623 
20010809 
20010911 
20011112 
20011214 
20020219 
20020326 
20020505 
20020611
20020915
20021230 上表中有不少月份缺失
如何把这些缺失的找出来
谢谢了~~ 

Answer 1

declare @T table (col datetime)
insert into @T
select '20010101' union all
select '20010402' union all
select '20010523' union all
select '20010623' union all
select '20010809' union all
select '20010911' union all
select '20011112' union all
select '20011214' union all
select '20020219' union all
select '20020326' union all
select '20020505' union all
select '20020611' union all
select '20020915' union all
select '20021230'select a.col as c1,b.col as c2 from @T a 
right join (
select dateadd(month,number,'20010101') as col 
from master..spt_values where type='p' and number < 24) b
on convert(varchar(7),a.col,120)=convert(varchar(7),b.col,120)
/*
c1                      c2
----------------------- -----------------------
2001-01-01 00:00:00.000 2001-01-01 00:00:00.000
NULL                    2001-02-01 00:00:00.000
NULL                    2001-03-01 00:00:00.000
2001-04-02 00:00:00.000 2001-04-01 00:00:00.000
2001-05-23 00:00:00.000 2001-05-01 00:00:00.000
2001-06-23 00:00:00.000 2001-06-01 00:00:00.000
NULL                    2001-07-01 00:00:00.000
2001-08-09 00:00:00.000 2001-08-01 00:00:00.000
2001-09-11 00:00:00.000 2001-09-01 00:00:00.000
NULL                    2001-10-01 00:00:00.000
2001-11-12 00:00:00.000 2001-11-01 00:00:00.000
2001-12-14 00:00:00.000 2001-12-01 00:00:00.000
NULL                    2002-01-01 00:00:00.000
2002-02-19 00:00:00.000 2002-02-01 00:00:00.000
2002-03-26 00:00:00.000 2002-03-01 00:00:00.000
NULL                    2002-04-01 00:00:00.000
2002-05-05 00:00:00.000 2002-05-01 00:00:00.000
2002-06-11 00:00:00.000 2002-06-01 00:00:00.000
NULL                    2002-07-01 00:00:00.000
NULL                    2002-08-01 00:00:00.000
2002-09-15 00:00:00.000 2002-09-01 00:00:00.000
NULL                    2002-10-01 00:00:00.000
NULL                    2002-11-01 00:00:00.000
2002-12-30 00:00:00.000 2002-12-01 00:00:00.000
*/

Answer 2

declare @test table(col varchar(8))
insert into @test
select '20010101' union all  
select '20010402' union all    
select '20010523' union all    
select '20010623' union all    
select '20010809' union all    
select '20010911' union all    
select '20011112' union all    
select '20011214' union all    
select '20020219' union all    
select '20020326' union all    
select '20020505' union all    
select '20020611' union all  
select '20020915' union all  
select '20021230'
;with cte as
(
select dateadd(mm,number,dt1) dt from master..spt_values,
(select min(col) dt1,max(col) dt2 from @test)t
where type='P' and number<=datediff(mm,dt1,dt2) 
)
select isnull(b.col,convert(varchar(8),a.dt,112))
from cte a
left join @test b
on convert(varchar(7),dt,120)=convert(varchar(7),convert(datetime,b.col),120)
/*
----------
20010101
20010201
20010301
20010402
20010523
20010623
20010701
20010809
20010911
20011001
20011112
20011214
20020101
20020219
20020326
20020401
20020505
20020611
20020701
20020801
20020915
20021001
20021101
20021230
*/随便提醒下LZ，要记得及时结贴，不然以后大家看到你的结贴率就不想帮你解决问题了！

Answer 3

CREATE TABLE TABLE5(DocDate DATETIME)
INSERT INTO TABLE5
SELECT '20010101'  UNION ALL
SELECT '20010402'  UNION ALL
SELECT '20010523'  UNION ALL
SELECT '20010623'  UNION ALL
SELECT '20010809'  UNION ALL
SELECT '20010911'  UNION ALL
SELECT '20011112'  UNION ALL
SELECT '20011214'  UNION ALL
SELECT '20020219'  UNION ALL
SELECT '20020326'  UNION ALL
SELECT '20020505'  UNION ALL
SELECT '20020611'  UNION ALL
SELECT '20020915'  UNION ALL
SELECT '20021230' DECLARE @MINMONTH DATETIME
DECLARE @MAXMONTH DATETIME
DECLARE @TMEPMONTH DATETIME
SELECT @MINMONTH =MIN(DocDate),@MAXMONTH =MAX(DocDate) FROM TABLE5 CREATE TABLE #TABLE6(DocDate DATETIME)WHILE DATEDIFF(MONTH,@MINMONTH,@MAXMONTH)>0
BEGIN
SET @MINMONTH =DATEADD(MONTH,1,@MINMONTH)
IF NOT EXISTS(SELECT 1 FROM TABLE5 WHERE  CONVERT(CHAR(7),DocDate,111) =  CONVERT(CHAR(7),@MINMONTH,111) )
BEGIN
INSERT INTO #TABLE6
SELECT @MINMONTH
END
ENDSELECT * FROM #TABLE6DROP TABLE #TABLE6/*
DocDate
-----------------------
2001-02-01 00:00:00.000
2001-03-01 00:00:00.000
2001-07-01 00:00:00.000
2001-10-01 00:00:00.000
2002-01-01 00:00:00.000
2002-04-01 00:00:00.000
2002-07-01 00:00:00.000
2002-08-01 00:00:00.000
2002-10-01 00:00:00.000
2002-11-01 00:00:00.000(10 行受影响)*/

Answer 4

CREATE TABLE TABLE5(DocDate DATETIME)
INSERT INTO TABLE5
SELECT '20010101' UNION ALL
SELECT '20010402' UNION ALL
SELECT '20010523' UNION ALL
SELECT '20010623' UNION ALL
SELECT '20010809' UNION ALL
SELECT '20010911' UNION ALL
SELECT '20011112' UNION ALL
SELECT '20011214' UNION ALL
SELECT '20020219' UNION ALL
SELECT '20020326' UNION ALL
SELECT '20020505' UNION ALL
SELECT '20020611' UNION ALL
SELECT '20020915' UNION ALL
SELECT '20021230'  DECLARE @MINMONTH DATETIME
DECLARE @MAXMONTH DATETIME
DECLARE @TMEPMONTH DATETIME
SELECT @MINMONTH =MIN(DocDate),@MAXMONTH =MAX(DocDate) FROM TABLE5  CREATE TABLE #TABLE6(DocDate DATETIME)WHILE DATEDIFF(MONTH,@MINMONTH,@MAXMONTH)>0
BEGIN
SET @MINMONTH =DATEADD(MONTH,1,@MINMONTH)
IF NOT EXISTS(SELECT 1 FROM TABLE5 WHERE CONVERT(CHAR(7),DocDate,111) = CONVERT(CHAR(7),@MINMONTH,111) )
BEGIN
INSERT INTO #TABLE6
SELECT @MINMONTH
END
ENDSELECT * FROM #TABLE6DROP TABLE #TABLE6/*
DocDate
-----------------------
2001-02-01 00:00:00.000
2001-03-01 00:00:00.000
2001-07-01 00:00:00.000
2001-10-01 00:00:00.000
2002-01-01 00:00:00.000
2002-04-01 00:00:00.000
2002-07-01 00:00:00.000
2002-08-01 00:00:00.000
2002-10-01 00:00:00.000
2002-11-01 00:00:00.000(10 行受影响)*/

Answer 5

CREATE TABLE TABLE5(DocDate DATETIME)
INSERT INTO TABLE5
SELECT '20010101' UNION ALL
SELECT '20010402' UNION ALL
SELECT '20010523' UNION ALL
SELECT '20010623' UNION ALL
SELECT '20010809' UNION ALL
SELECT '20010911' UNION ALL
SELECT '20011112' UNION ALL
SELECT '20011214' UNION ALL
SELECT '20020219' UNION ALL
SELECT '20020326' UNION ALL
SELECT '20020505' UNION ALL
SELECT '20020611' UNION ALL
SELECT '20020915' UNION ALL
SELECT '20021230'  DECLARE @MINMONTH DATETIME
DECLARE @MAXMONTH DATETIME
DECLARE @TMEPMONTH DATETIME
SELECT @MINMONTH =MIN(DocDate),@MAXMONTH =MAX(DocDate) FROM TABLE5  CREATE TABLE #TABLE6(DocDate DATETIME)WHILE DATEDIFF(MONTH,@MINMONTH,@MAXMONTH)>0
BEGIN
SET @MINMONTH =DATEADD(MONTH,1,@MINMONTH)
IF NOT EXISTS(SELECT 1 FROM TABLE5 WHERE CONVERT(CHAR(7),DocDate,111) = CONVERT(CHAR(7),@MINMONTH,111) )
BEGIN
INSERT INTO #TABLE6
SELECT @MINMONTH
END
ENDSELECT * FROM #TABLE6DROP TABLE #TABLE6/*
DocDate
-----------------------
2001-02-01 00:00:00.000
2001-03-01 00:00:00.000
2001-07-01 00:00:00.000
2001-10-01 00:00:00.000
2002-01-01 00:00:00.000
2002-04-01 00:00:00.000
2002-07-01 00:00:00.000
2002-08-01 00:00:00.000
2002-10-01 00:00:00.000
2002-11-01 00:00:00.000(10 行受影响)*/

Answer 6

declare @test table(col varchar(8))
insert into @test
select '20010101' union all  
select '20010402' union all    
select '20010523' union all    
select '20010623' union all    
select '20010809' union all    
select '20010911' union all    
select '20011112' union all    
select '20011214' union all    
select '20020219' union all    
select '20020326' union all    
select '20020505' union all    
select '20020611' union all  
select '20020915' union all  
select '20021230'
;with cte as
(
select dateadd(mm,number,dt1) dt from master..spt_values,
(select min(col) dt1,max(col) dt2 from @test)t
where type='P' and number<=datediff(mm,dt1,dt2) 
)select convert(varchar(8),dt,112) from cte t
where not exists(select 1 from @test 
 where convert(varchar(7),t.dt,120)=convert(varchar(7),convert(datetime,col),120))/*
--------
20010201
20010301
20010701
20011001
20020101
20020401
20020701
20020801
20021001
20021101
*/

Answer 7

--author:galenkeny
--sql server 2005 -->测试数据
if object_id('tb')is not null 
drop table tb
go
create  table tb(o_date datetime)
insert into tb
select '20010101' union all
select '20010402' union all
select '20010523' union all
select '20010623' union all
select '20010809' union all
select '20010911' union all
select '20011112' union all
select '20011214' union all
select '20020219' union all
select '20020326' union all
select '20020505' union all
select '20020611' union all
select '20020915' union all
select '20021230'
-->测试查询
declare @date datetime
set @date=(select top 1 convert(varchar,o_date,112) from tb)select [day]=convert(varchar(6),DATEADD(mm,DATEDIFF(mm,0,@date)+number,0),112)
from master..spt_values
where type='p'
  and number<24 --datediff(mm,@date,dateadd(yy,1,@date))
  and convert(varchar(6),DATEADD(mm,DATEDIFF(mm,0,@date)+number,0),112) not in (select convert(varchar(6),o_date,112) from tb)------------------------------------------------------------------------------
-->结果集
/* 
  day20010201
20010301
20010401
20010501
20010601
20010701
20010801
20010901
20011001
20011101
20011201
20020101
20020201
20020301
20020401
20020501
20020601
20020701
20020801
20020901
20021001
20021101
20021201
*/