Here I will make a conclusion for the day before today I have noted in blog. We can refer it to Select from (select from) , Case when then else end ,Functions and Internet!
This is an actual instance in my daily work :
select * from
(
select *
, case when (ISNULL(FBandFGBasic,0) -NPVWaterfallForbearancelimitCalculated )>0
then (ISNULL(FBandFGBasic,0) -NPVWaterfallForbearancelimitCalculated )
Else -1.112 END
as AmountExcessNPVWaterfall
........
from
(
select
sd.MaximumPrincipalForbearance as ModwaterfallForbearanceLimit
,(0.3*debtcapitalized) as CapDebt@30
,(debtcapitalized -0.8*hme.CurrentValue) as [80%LTVModWaterfall]
..........
,(npv.PrincipalForgivenessAmount + npv.PrincipalForbearanceAmount ) as FBandFGBasic
, case when 0.3*debtcapitalizedtodatacollectiondate >(debtcapitalizedtodatacollectiondate -0.8*hme.CurrentValue)
then 0.3*debtcapitalizedtodatacollectiondate
else (debtcapitalizedtodatacollectiondate -0.8*hme.CurrentValue)
END
as NPVWaterfallForbearancelimitCalculated
,case when m.loan_no in
(
select loan_no from loancategorylist where active=1 and loancategoryid=32
) then 'Y'
else 'N' END
as IsJPMTexasLoan
,dbo.GetExcessiveFobearance_JPM
(
hme.servicerloannumber
,hme.Debtcapitalizedtodatacollectiondate
,npv.currentpropertyvalue
,coalesce(npv.PrincipalForgivenessAmount,0)
,coalesce(npv.PrincipalForbearanceAmount,0)
)
as ExcessiveForbearanceNpvWaterfallBasic
...........
..........
from haaaaaavaluation hme
join haaaaaavaluationstandarddata sd on hme.haaaaaavaluationid = sd.haaaaaavaluationid
left join haaaaaavaluationstandarddatanpv npvsd on hme.haaaaaavaluationid = npvsd.haaaaaavaluationid
..........
)T
)as E
where (ExcessiveForbearanceModWaterfallBasic != ExcessiveForbearanceNPVWaterfallBasic
or ExcessiveForbearanceModWaterfallPRA != ExcessiveForbearanceNPVWaterfallPRA)
and .........
order by createddate desc
I hope I can make more conclusions after work so that I can make the knowledge more clear and stable in my mind whenever I need use I can fetch them quickly.