Here is my actual code currently.
SELECT distinct iv.studentid, iv.externalId3
, case when iv.primaryEFCType = '1' then 'Dependent'
when iv.primaryEFCType = '2' then 'Independent without Dependents'
when iv.primaryEFCType = '3' then 'Independent with Dependents'
when iv.primaryEFCType = '4' then 'Dependents'
when iv.primaryEFCType = '5' then 'Independent without Dependents'
when iv.primaryEFCType = '6' then 'Independent with Dependents'
else NULL end
, iv.federalAwardYearName
, '1' as FAFSACompletedYN
, iv.dateApplicationCompleted as FAFSACompletedDate
, case iv.studentIsSelectedForVerification when 'Y' then '1' else '0' end as SelectedForVerification
, case iv.studentIsSelectedForVerification when 'Y' then (select distinct max(dr1.reviewedDate)
from dataExtract_DocumentRequirement_View dr1
where dr1.studentId = iv.studentid
and dr1.scopeValue = iv.federalAwardYearName
and dr1.status in ('SATISFIED','WAIVED')
and dr1.activityLogEntryTypeCode = 'VerificationRelated'
and not exists (select 'x' from dataExtract_DocumentRequirement_View b
where b.studentId = dr1.studentId
and b.status = 'NEEDED'
and b.activityLogEntryTypeCode = 'VerificationRelated')) else null end as VerifiedCompleteDate
, case when (select sum(av1.acceptedAmount) from dataExtract_Award_View_v002 av1, dataExtract_LoanPeriod_View_v001 lv1
where av1.studentId = av.studentId
and av1.loanPeriodId = lv1.loanPeriodId
and lv1.federalAwardYear = iv.federalAwardYearName) > 0 then '1' else '0' end as AcceptedYN
, '' as AcceptedDate
, case dr2.status when 'SATISFIED' then '1' else '0' end as ELCCompletedYN
, dr2.reviewedDate as ELCCompletedDate
, case dr3.status when 'SATISFIED' then '1' else '0' end as MPNCompletedYN
, dr3.reviewedDate as MPNCompletedDate
, case when (select sum(av2.paidAmount) from dataExtract_Award_View_v002 av2, dataExtract_LoanPeriod_View_v001 lv2
where av2.studentId = av.studentId
and av2.loanPeriodId = lv2.loanPeriodId
and lv2.federalAwardYear = iv.federalAwardYearName) > 0 then '1' else '0' end as DisbursedYN
, fv.fund_name
, av.offeredAmount
from dataExtract_ISIR_View iv
JOIN dataExtract_Award_View_v002 AS av
ON (iv.studentid = av.studentid)
JOIN dataExtract_LoanPeriod_View_v001 AS lv
ON (av.studentid = lv.studentid
and av.loanPeriodId = lv.loanPeriodId
and lv.federalAwardYear = iv.federalAwardYearName)
INNER JOIN dataExtract_DocumentRequirement_View AS dr2
ON (iv.studentid = dr2.studentid)
INNER JOIN dataExtract_DocumentRequirement_View AS dr3
ON (iv.studentid = dr3.studentid)
INNER JOIN dataExtract_AwardDisbursement_View_v001 adv
ON (iv.studentid = adv.studentid)
JOIN dataExtract_Fund_Config_View fv
ON (av.fundid = fv.fundid)
where federalAwardYearName = '2017-2018'
and dr2.documentExternalId = 'FAELC'
and dr3.documentExternalId in ('FAMPN','FAMPNPLUS')
and adv.awardId = av.awardId
;