declare @last table(year1 varchar(100),companycode1 varchar(100),lastmonth varchar(100))
declare @now table(year varchar(100),companycode varchar(100), month varchar(100))
declare @temp table(month varchar(100));
declare @year int
set @year=2009
insert into @temp (month) values('01');
insert into @temp (month) values('02');
insert into @temp (month) values('03');
insert into @temp (month) values('04');
insert into @temp (month) values('05');
insert into @temp (month) values('06');
insert into @temp (month) values('07');
insert into @temp (month) values('08');
insert into @temp (month) values('09');
insert into @temp (month) values('10');
insert into @temp (month) values('11');
insert into @temp (month) values('12');
insert into @last
select * from (
select Year,companycode from
ACV_Gr with(nolock) where [year]=@year GROUP BY companycode,Year
)a inner join @temp t on 1=1
print 1
--select * from @last order by companycode1,lastmonth
insert into @now
select Year,companycode,Month=substring('0'+month,len(month) ,5) from
ACV_BookDetail_2009 with(nolock) where [year]=@year GROUP BY companycode,Year,Month
--select * from @now order by companycode,month
print 2
delete @last where exists(select * from @now a where a.year= YEAR1 AND a.companycode= companycode1 and a.month=lastmonth)
select * from @last order by companycode1,lastmonth
