select c.ApartmentComplexName, c.StreetLine1Text, c.StreetLine2Text,
(
select MAX(EffectiveDate) from Housing.CafProjectEvent where CafProjectEventTypeKey = 6 and CafProjectKey = p.CafProjectKey
) as [BoardApprovalDate],
(
select SUM(FundingSourceAmt) from Loan.LoanFundingSource where LoanKey = l.LoanKey
) as [TotalFundsAllocated],
(
select SUM(FundingSourceAmt) from Loan.LoanFundingSource
where FundingSourceTypeKey in (1,2,3,4,5) and LoanKey = l.LoanKey
) as [TotalCountyFundsAllocated],
(
select SUM(FundingSourceAmt) from Loan.LoanFundingSource where LoanKey = l.LoanKey and FundingSourceTypeKey = @FundingSourceId
) as [TotalFundingSourceFunding],
dt_surveys.TotalUnits,
(
select SUM(CafUnitDetail.UnitCnt) from Housing.CafUnitDetail
inner join Housing.ApartmentSurveyDetail on CafUnitDetail.ApartmentSurveyDetailKey = ApartmentSurveyDetail.ApartmentSurveyDetailKey
where ApartmentSurveyDetail.ApartmentComplexKey = c.ApartmentComplexKey
and ApartmentSurveyDetail.SurveyYear = ( select MAX(SurveyYear) from Housing.ApartmentSurveyDetail where ApartmentComplexKey = c.ApartmentComplexKey )
and CafUnitDetail.UnitAffordabilityDetailKey = 11
) as [TotalMarketUnits],
l.CommentText
from
Housing.ApartmentComplex c
inner join Housing.CafProject p on c.ApartmentComplexKey = p.ApartmentComplexKey
inner join Loan.CafProjectLoan pl on p.CafProjectKey = pl.CafProjectKey
inner join Loan.Loan l on pl.LoanKey = l.LoanKey
left outer join
(
select ApartmentComplexKey, SurveyYear, SUM(UnitCnt) as TotalUnits, SUM(AccessibleUnitCnt) as TotalAccessibleUnits
from Housing.ApartmentSurveyDetail
group by ApartmentComplexKey, SurveyYear
) dt_surveys
on dt_surveys.ApartmentComplexKey = c.ApartmentComplexKey and
dt_surveys.SurveyYear = ( select MAX(SurveyYear) from Housing.ApartmentSurveyDetail where ApartmentComplexKey = c.ApartmentComplexKey )
where
l.LoanKey in (select LoanKey from Loan.LoanFundingSource where FundingSourceTypeKey = @FundingSourceId)
and
p.CafProjectKey in (select CafProjectKey from Housing.CafProjectEvent where CafProjectEventTypeKey = 6 and EffectiveDate between @StartDate and @EndDate)