Actual Practice : [with CTE]&[convert]&[coalesce] as in my work - 4

When some conditions of ‘Where’ part are fetched from another sql statement, usually, we can write as below :

select * from Haaaaaaluation
where HaaaaaavaluationId in 
(
    select HaaaaaavaluationId from HaaaaaaData
    -- where ......
)

Of course we can write like this. But it will make statements efficiency low.
Then how can we improve the efficiency?
Yes, we can use [with CTE as] to implement this goal.

With CTE as
(
    select HaaaaaavaluationId from HaaaaaaData
    -- where ......
)
select * from Haaaaaaluation
where HaaaaaavaluationId in 
(
    select * from CTE
)

Let us have a try by using my actual experience in my work :

;with CTE AS
(
    select 
    HaaaaaavaluationId
    from haaaaaavaluation
    where ServicerLoanNumber = '0017201310' and activeRecord = 1
)
select 
me.HaaaaaavaluationId 
,data.DecisionMatrixWaterfallId 
,WaterFallId = case 
                when data.PrincipalForbearanceAmount>0 
                or (data.PrincipalWriteDown =0 and data.PrincipalForbearanceAmount=0 )then 11 
                else 12 end
,convert (numeric(14,5)
,coalesce((hmea.resetinterestrate/100),data.interestratebeforemodification)) as PreModInterestRate
from Haaaaaavaluation me 
inner join HaaaaaavaluationStandardDataNPV sdnpv on sdnpv.Haaaaaavaluationid = me.Haaaaaavaluationid
.........
where me.Haaaaaavaluationid in(
    select * from CTE
)

Now, is it more clear and more effective？

Let me show you the provement :
We can use those methods to print the run time of our two statements
Print run time

No [with CTE as] :

With [with CTE as] :

Actually, in most easy statements we need not be like above. Maybe the two have no big difference. However, if we have more complex requirement to be completed, we can see the difference.

Also, let me show a true example in my work:

;With CTE as  
(
    select ModificationTypeId
    from modificationdefinition a 
    join ModificationCalculation c on a.ModificationCalculationid = c.ModificationCalculationid 
    where a.ModificationTypeId=1
),
ForgivenessLimitId AS  --
(
    select LOAN_NO,CTE.modificationlimitid,CTE.ShortDescription
    from CTE 
    where ShortDescription !='NOT ALLOWED'  --AG Default
    AND limitid = 3 
),
ForbearanceLimitId AS
(
    select LOAN_NO,CTE.modificationlimitid,CTE.ShortDescription
    from CTE 
    where ShortDescription ='NOT ALLOWED'  --SPS Default
    AND limitid = 6 
)

select distinct data.ServicerLoanNumber,.....,data.CreatedBy
FROM Haaaaadata data 
      INNER JOIN Haaaaaavaluation HME on data.Haaaaaavaluationid =HME.HaaaaaavaluationId
      INNER JOIN CTE ON HME.ServicerLoanNumber = CTE.Loan_no
      ........
WHERE 1=1
AND data.CreatedBy='********' 
AND data.ServicerLoanNumber  IN 
(
    select a.lOAN_no from ForgivenessLimitId a,ForbearanceLimitId b
    where a.lOAN_no = b.Loan_no
)

Here we need pay attention to :

1. the begin with “;” : if has no other sql statements before or in front of [With CTE as] , we can omit the ‘;’. But if it has, we must not forget the ‘;’.
2. Case when …else end : It is easy and frequent use in work. We can use it in “select” column which is defined as you want show by yourself.
3. convert (numeric(14,5), coalesce(num1,num2,…) : convert the ‘num1’ or ‘num2’ to [numeric(14,5)] Type.
4. coalesce((hmea.resetinterestrate/100),data.interestratebeforemodification)) : If [hmea.resetinterestrate/100] is null, then we fetch “data.interestratebeforemodification”. We will use it frequently in work jobs.

The more details we can serach it by Google or Baidu.