丑代码展示

所有信息来源一个表。这个表存了各个fund在每个年报中的信息，如performance fee、NetExpenseRatio、OngoingCharge等等许多。对于提到的三个字段，每期年报不一定有值，现要求取有值的最新的那期，应该如何写代码？

--For table NetExpenseRatio
select Id,AnnualReportDate,NetExpenseRatio into #Temp1 from RawData.dbo.ViewAnnualReport
where NetExpenseRatio is not NULL
select Table1.Id, Table1.AnnualReportDate into #Temp001 from (select Id,AnnualReportDate,NetExpenseRatio,ROW_NUMBER() over (partition by Id order by AnnualReportDate Desc) RowNumber
from #Temp1) Table1
where Table1.RowNumber = 1

--For table OngoingCharge
select Id,AnnualReportDate,OngoingCharge into #Temp2 from RawData.dbo.ViewAnnualReport
where OngoingCharge is not NULL
select Table2.Id, Table2.AnnualReportDate into #Temp002 from (select Id,AnnualReportDate,OngoingCharge,ROW_NUMBER() over (partition by Id order by AnnualReportDate Desc) RowNumber
from #Temp2) Table2
where Table2.RowNumber = 1

--For table PerformanceFee
select Id,AnnualReportDate,PerformanceFee into #Temp3 from RawData.dbo.ViewAnnualReport
where PerformanceFee is not NULL
select Table3.Id, Table3.AnnualReportDate into #Temp003 from (select Id,AnnualReportDate,PerformanceFee,ROW_NUMBER() over (partition by Id order by AnnualReportDate Desc) RowNumber
from #Temp3) Table3
where Table3.RowNumber = 1
-------------------------------------------------------------------------------------------------------------------------------------------

--OverLap Table All
select * into #OverLap from #Temp001 Intersect select * from #Temp002 Intersect select * from #Temp003

--OverLap Temp Table 1&2
(Select * into #Temp12 From #Temp001 Intersect select * from #Temp002) Except (Select * from #OverLap)

--OverLap Temp Table 1&3
(Select * into #Temp13 From #Temp001 Intersect select * from #Temp003) Except (Select * from #OverLap)

--OverLap Temp Table 2&3
(Select * into #Temp23 From #Temp002 Intersect select * from #Temp003) Except (Select * from #OverLap)

--Exception #ExTemp1

Select * into #ExTemp1 from #Temp001 Except (Select * from #Temp002 union all (select * from #Temp003))

--Exception #ExTemp2

Select * into #ExTemp2 from #Temp002 Except (Select * from #Temp001 union all (select * from #Temp003))

--Exception #ExTemp3

Select * into #ExTemp3 from #Temp003 Except (Select * from #Temp001 union all (select * from #Temp002))

-------------------------------------------------------------------------------------------------------------------------------------------

--FindOutData
select Main.Id,Main.AnnualReportDate,Main.NetExpenseRatio,Main.OngoingCharge,Main.PerformanceFee from RawData.dbo.ViewAnnualReport Main right join #OverLap a on Main.Id=a.Id and Main.AnnualReportDate=a.AnnualReportDate
union (select Main.Id,Main.AnnualReportDate,Main.NetExpenseRatio,Main.OngoingCharge,Main.PerformanceFee from RawData.dbo.ViewAnnualReport Main right join #Temp12 b on Main.Id=b.Id and Main.AnnualReportDate=b.AnnualReportDate)
union (select Main.Id,Main.AnnualReportDate,Main.NetExpenseRatio,Main.OngoingCharge,Main.PerformanceFee from RawData.dbo.ViewAnnualReport Main right join #Temp13 c on Main.Id=c.Id and Main.AnnualReportDate=c.AnnualReportDate)
union (select Main.Id,Main.AnnualReportDate,Main.NetExpenseRatio,Main.OngoingCharge,Main.PerformanceFee from RawData.dbo.ViewAnnualReport Main right join #Temp23 d on Main.Id=d.Id and Main.AnnualReportDate=d.AnnualReportDate)
union (select Main.Id,Main.AnnualReportDate,Main.NetExpenseRatio,Main.OngoingCharge,Main.PerformanceFee from RawData.dbo.ViewAnnualReport Main right join #ExTemp1 e on Main.Id=e.Id and Main.AnnualReportDate=e.AnnualReportDate)
union (select Main.Id,Main.AnnualReportDate,Main.NetExpenseRatio,Main.OngoingCharge,Main.PerformanceFee from RawData.dbo.ViewAnnualReport Main right join #ExTemp2 f on Main.Id=f.Id and Main.AnnualReportDate=f.AnnualReportDate)
union (select Main.Id,Main.AnnualReportDate,Main.NetExpenseRatio,Main.OngoingCharge,Main.PerformanceFee from RawData.dbo.ViewAnnualReport Main right join #ExTemp3 g on Main.Id=g.Id and Main.AnnualReportDate=g.AnnualReportDate)