--定義基本數(shù)字表
declare @T1 table(代碼 int,名稱 varchar(10),參加時間 datetime,終止時間 datetime)
insert into @T1
	select 12,'單位1','2003/04/01','2004/05/01'
	union all select 22,'單位2','2001/02/01','2003/02/01'
	union all select 42,'單位3','2000/04/01','2003/05/01'
	union all select 25,'單位5','2003/04/01','2003/05/01'

--定義年表
declare @NB table(代碼 int,名稱 varchar(10),年份 int)
insert into @NB
	select 12,'單位1',2003
	union all select 12,'單位1',2004
	union all select 22,'單位2',2001
	union all select 22,'單位2',2002
	union all select 22,'單位2',2003

--定義月表
declare @YB table(代碼 int,名稱 varchar(10),年份 int,月份 varchar(2))
insert into @YB
	select 12,'單位1',2003,'04'
	union all select 22,'單位2',2001,'01'
	union all select 22,'單位2',2001,'12'

--為年表+月表數(shù)據(jù)處理準備臨時表
select top 8246 y=identity(int,1753,1)
into #tby from
	(select id from syscolumns) a,
	(select id from syscolumns) b,
	(select id from syscolumns) c

--為月表數(shù)據(jù)處理準備臨時表
select top 12 m=identity(int,1,1)
into #tbm from syscolumns

/*--數(shù)據(jù)處理--*/
--年表數(shù)據(jù)處理
select a.*
from(
select a.代碼,a.名稱,年份=b.y
from @T1 a,#tby b
where b.y between year(參加時間) and year(終止時間)
) a left join @NB b on a.代碼=b.代碼 and a.年份=b.年份
where b.代碼 is null

--月表數(shù)據(jù)處理
select a.*
from(
select a.代碼,a.名稱,年份=b.y,月份=right('00'+cast(c.m as varchar),2)
from @T1 a,#tby b,#tbm c
where b.y*100+c.m between convert(varchar(6),參加時間,112) 
	and convert(varchar(6),終止時間,112)
) a left join @YB b on a.代碼=b.代碼 and a.年份=b.年份 and a.月份=b.月份
where b.代碼 is null
order by a.代碼,a.名稱,a.年份,a.月份

--刪除數(shù)據(jù)處理臨時表
drop table #tby,#tbm