create table tb(sMonth varchar(6))
insert into tb values('200605')
insert into tb values('200606')
insert into tb values('200607')
insert into tb values('200609')
insert into tb values('200612')
insert into tb values('200702')
goselect top 100 identity(int,0,1) as id into tmp from syscolumns a,syscolumns bselect distinct convert(varchar(6),dateadd(month ,b.id , cast(a.smonth+'01' as datetime)),112) 不存在的年月 from tb a , tmp b where dateadd(month ,b.id , cast(a.smonth+'01' as datetime)) <= getdate()
and convert(varchar(6),dateadd(month ,b.id , cast(a.smonth+'01' as datetime)),112) not in (select smonth from tb)drop table tb,tmp/*
不存在的年月
------
200608
200610
200611
200701
200703
200704
200705
200706
200707
200708
200709
200710
200711(所影响的行数为 13 行)
*/
insert into tb values('200605')
insert into tb values('200606')
insert into tb values('200607')
insert into tb values('200609')
insert into tb values('200612')
insert into tb values('200702')
goselect top 100 identity(int,0,1) as id into tmp from syscolumns a,syscolumns bselect distinct convert(varchar(6),dateadd(month ,b.id , cast(a.smonth+'01' as datetime)),112) 不存在的年月 from tb a , tmp b where dateadd(month ,b.id , cast(a.smonth+'01' as datetime)) <= getdate()
and convert(varchar(6),dateadd(month ,b.id , cast(a.smonth+'01' as datetime)),112) not in (select smonth from tb)drop table tb,tmp/*
不存在的年月
------
200608
200610
200611
200701
200703
200704
200705
200706
200707
200708
200709
200710
200711(所影响的行数为 13 行)
*/
insert @a select '200605'
union all select '200606'
union all select '200607'
union all select '200609'
union all select '200612'
union all select '200702'
union all select '200711' declare @min varchar(20),@num int
declare @sql nvarchar(200)create table #(id int identity(0,1),sMonth varchar(20))
select @min=min(sMonth),@num=datediff(month,min(sMonth)+'01',max(sMonth)+'01') from @a
set @sql='insert # select top '+ltrim(@num)+ ' '+@min+'01 from syscolumns'
exec(@sql)
select b.sMonth from @a a
right join (select convert(varchar(6),dateadd(month,id,sMonth),112) sMonth from #) b
on a.sMonth=b.sMonth where a.sMonth is null
drop table #--result
/*sMonth
--------------------
200608
200610
200611
200701
200703
200704
200705
200706
200707
200708
200709
200710
*/
declare @A table(sMonth nvarchar(20))
insert into @A
SELECT '200605'
UNION ALL SELECT '200606'
UNION ALL SELECT '200607'
UNION ALL SELECT '200609'
UNION ALL SELECT '200612'
UNION ALL SELECT '200702' select CAST(a.sMonth AS int)+1 from
@A a left join @A b
on CAST(a.sMonth AS int) = CAST(b.sMonth AS int)-1
where b.sMonth is null
TStringList *res = new TStringList;
res->Add("BeginMonth");
res->Add("num");
if (DoCheckX("select BeginMonth, datediff(mm, BeginMonth, BeginMonth) num from A", res))
{
int dTemp = ToI(res->Strings[1]);
for (int i = 0; i < dTemp; i++)
{
plus_month(&res->Strings[0], i, &sTemp);
if (i == 0)
sql += "select month = " + sTemp + "";
else
sql += " union all select month = " + sTemp + "";
}
}
sql = "select month from (" + sql + ")a where month not in (select distinct month from B)";
delete res;
insert into tb values('200605')
insert into tb values('200606')
insert into tb values('200607')
insert into tb values('200609')
insert into tb values('200612')
insert into tb values('200702')
go
declare @s varchar(6)
set @s = '200605'
declare @e varchar(6)
set @e = '200711'while @s < @e
begin
if not exists(select 1 from tb where smonth = @s) print @s
set @s = convert(char(6),dateadd(mm,1,left(@s,4)+'-'+right(@s,2)+'-01') ,112)
enddrop table tb/*200608
200610
200611
200701
200703
200704
200705
200706
200707
200708
200709
200710
*/