现有一个表内容如下：

AA        BB1           BB2         
已收     2008-01-01     2008-01-31
已收     2008-02-01     2008-02-29
已收     2008-04-01     2008-04-30
已收     2008-07-01     2008-07-31
假如现在的时间是 2008-08-10，我想让SQL判断出表中没有数据的月份，并插入一行数据，要求结果如下：
AA        BB1           BB2         
已收     2008-01-01     2008-01-31
已收     2008-02-01     2008-02-29
未收     2008-03-01     2008-03-31
已收     2008-04-01     2008-04-30
未收     2008-05-01     2008-05-31
未收     2008-06-01     2008-06-30
已收     2008-07-01     2008-07-31

如何可以实现？

create function fn_insertnorecord(@nowtime as datetime)
returns varchar(50)
as
begin
    declare @nowmonth as int
    declare @norecord as varchar(50)
    declare @i    as    int
    set @i=1;
    set @norecord=''
    select @nowmonth=datepart(month,@nowtime);--取得当前的月份
    declare tables_cursor cursor
    for        
        select datepart(month,BB1) from SQl_1 where BB1<=@nowtime order by BB1
    open tables_cursor
    declare @month int
    fetch tables_cursor into @month    
    while (@@fetch_status=0)
    begin        
        if @month<>@i
            begin
                set @norecord=@norecord+cast(@i as varchar)+','                
            end
        else
            begin
                fetch next from tables_cursor into @month
            end
        set @i=@i+1;    
    end
    close tables_cursor
    deallocate  tables_cursor    
    return @norecord
end
go

呃。。借鉴了LS的，写了个：
declare @ibb1 datetime   --要插入的bb1字段变量
declare @ibb2 datetime   --要插入的bb2字段变量
declare @Month int       --月份变量

set @Month = 1

while @month <= 10
begin
  if not exists (select * from Delmonth where bb1 = cast('2008-'+ltrim(str(@month))+'-1' as datetime ))
  begin
    set @ibb1 = cast('2008-'+ltrim(str(@month))+'-1' as datetime )
    set @ibb2 = dateadd(dd,-1,dateadd(mm,1,@ibb1))
    insert into Delmonth(AA,BB1,BB2) values('未收',@ibb1,@ibb2)
  end
  set @Month = @month + 1
end
当然这个程序只适合这个条件。

好羡慕你们哦！

还不如自己订制一个任务计划,定时执行