请问一个简单的有关遗漏日期的有关问题
请教一个简单的有关遗漏日期的问题
列A 列B
1 201208
2 201208
3 201205
1 201207
2 201206
3 201204
1 201203
2 201204
3 201201
求教，B列录入的时候是输入的int型，我想把它作为日期形式，能不能写条句子查询出列A为1、2、3从200711到现在遗漏的日期。谢谢大神们！ 就比如列A为1的 201207-201203就缺了3个月~
------解决方案--------------------
列A 列B
1 201208
2 201208
3 201205
1 201207
2 201206
3 201204
1 201203
2 201204
3 201201
求教，B列录入的时候是输入的int型，我想把它作为日期形式，能不能写条句子查询出列A为1、2、3从200711到现在遗漏的日期。谢谢大神们！ 就比如列A为1的 201207-201203就缺了3个月~
------解决方案--------------------
- SQL code
--try
SELECT b.[A],a.[B]
FROM (
SELECT CONVERT(VARCHAR(6) , DATEADD(mm , number , '20071101') , 112) AS [B]
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 0 AND DATEDIFF(mm , '20071101' , GETDATE())
) a
CROSS JOIN (
SELECT [A] FROM [tb] GROUP BY [A]
) b
EXCEPT
SELECT * FROM [tb]
------解决方案--------------------
- SQL code
-->try
declare @test table(A int,B int)
insert into @test
select 1, 201208 union all
select 2, 201208 union all
select 3, 201205 union all
select 1, 201207 union all
select 2, 201206 union all
select 3, 201204 union all
select 1, 201203 union all
select 2, 201204 union all
select 3, 201201
declare @ym int
set @ym=200711
select * from
(
select t.A,convert(varchar(6),dateadd(mm,number,ltrim(@ym)+'01'),112) dt
from master..spt_values,(select distinct A from @test) t
where type='P'
and number<=datediff(mm,ltrim(@ym)+'01',getdate())
) a
where not exists(select 1 from @test where B=a.dt and A=A.A)
order by A,dt
/*
A dt
----------- ------
1 200711
1 200712
1 200801
1 200802
1 200803
1 200804
1 200805
1 200806
1 200807
1 200808
1 200809
1 200810
1 200811
1 200812
1 200901
1 200902
1 200903
1 200904
1 200905
1 200906
1 200907
1 200908
1 200909
1 200910
1 200911
1 200912
1 201001
1 201002
1 201003
1 201004
1 201005
1 201006
1 201007
1 201008
1 201009
1 201010
1 201011
1 201012
1 201101
1 201102
1 201103
1 201104
1 201105
1 201106
1 201107
1 201108
1 201109
1 201110
1 201111
1 201112
1 201201
1 201202
1 201204
1 201205
1 201206
1 201209
2 200711
2 200712
2 200801
2 200802
2 200803
2 200804
2 200805
2 200806
2 200807
2 200808
2 200809
2 200810
2 200811
2 200812
2 200901
2 200902
2 200903
2 200904
2 200905
2 200906
2 200907
2 200908
2 200909
2 200910
2 200911
2 200912
2 201001
2 201002
2 201003
2 201004
2 201005
2 201006
2 201007
2 201008
2 201009
2 201010
2 201011
2 201012
2 201101
2 201102
2 201103
2 201104
2 201105
2 201106
2 201107
2 201108
2 201109
2 201110
2 201111
2 201112
2 201201
2 201202
2 201203
2 201205
2 201207
2 201209
3 200711
3 200712
3 200801
3 200802
3 200803
3 200804
3 200805
3 200806
3 200807
3 200808
3 200809
3 200810
3 200811
3 200812
3 200901
3 200902
3 200903
3 200904
3 200905
3 200906
3 200907
3 200908
3 200909
3 200910
3 200911
3 200912
3 201001
3 201002
3 201003
3 201004
3 201005
3 201006
3 201007
3 201008
3 201009
3 201010
3 201011
3 201012
3 201101
3 201102
3 201103
3 201104
3 201105
3 201106
3 201107
3 201108
3 201109
3 201110
3 201111
3 201112
3 201202
3 201203
3 201206
3 201207
3 201208
3 201209
*/