daily pastebin goal
55%
SHARE
TWEET

Untitled

a guest Aug 12th, 2017 49 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select c.ApartmentComplexName, c.StreetLine1Text, c.StreetLine2Text,
  2. (
  3. select MAX(EffectiveDate) from Housing.CafProjectEvent where CafProjectEventTypeKey = 6 and CafProjectKey = p.CafProjectKey
  4. ) as [BoardApprovalDate],
  5. (
  6. select SUM(FundingSourceAmt) from Loan.LoanFundingSource where LoanKey = l.LoanKey
  7. ) as [TotalFundsAllocated],
  8. (
  9. select SUM(FundingSourceAmt) from Loan.LoanFundingSource
  10. where FundingSourceTypeKey in (1,2,3,4,5) and LoanKey = l.LoanKey
  11. ) as [TotalCountyFundsAllocated],
  12. (
  13. select SUM(FundingSourceAmt) from Loan.LoanFundingSource where LoanKey = l.LoanKey and FundingSourceTypeKey = @FundingSourceId
  14. ) as [TotalFundingSourceFunding],
  15. dt_surveys.TotalUnits,
  16. (
  17. select SUM(CafUnitDetail.UnitCnt) from Housing.CafUnitDetail
  18. inner join Housing.ApartmentSurveyDetail on CafUnitDetail.ApartmentSurveyDetailKey = ApartmentSurveyDetail.ApartmentSurveyDetailKey
  19. where ApartmentSurveyDetail.ApartmentComplexKey = c.ApartmentComplexKey
  20. and ApartmentSurveyDetail.SurveyYear = ( select MAX(SurveyYear) from Housing.ApartmentSurveyDetail where ApartmentComplexKey = c.ApartmentComplexKey )
  21. and CafUnitDetail.UnitAffordabilityDetailKey = 11
  22. ) as [TotalMarketUnits],
  23. l.CommentText
  24. from
  25. Housing.ApartmentComplex c
  26. inner join Housing.CafProject p on c.ApartmentComplexKey = p.ApartmentComplexKey
  27. inner join Loan.CafProjectLoan pl on p.CafProjectKey = pl.CafProjectKey
  28. inner join Loan.Loan l on pl.LoanKey = l.LoanKey
  29. left outer join
  30. (
  31. select ApartmentComplexKey, SurveyYear, SUM(UnitCnt) as TotalUnits, SUM(AccessibleUnitCnt) as TotalAccessibleUnits
  32. from Housing.ApartmentSurveyDetail
  33. group by ApartmentComplexKey, SurveyYear
  34. ) dt_surveys
  35. on dt_surveys.ApartmentComplexKey = c.ApartmentComplexKey and
  36. dt_surveys.SurveyYear = ( select MAX(SurveyYear) from Housing.ApartmentSurveyDetail where ApartmentComplexKey = c.ApartmentComplexKey )
  37. where
  38. l.LoanKey in (select LoanKey from Loan.LoanFundingSource where FundingSourceTypeKey = @FundingSourceId)
  39. and
  40. p.CafProjectKey in (select CafProjectKey from Housing.CafProjectEvent where CafProjectEventTypeKey = 6 and EffectiveDate between @StartDate and @EndDate)
RAW Paste Data
Top