SQL Table Function

 

 

Create Function [dbo].[TableValued](@YearMonth int)
Returns Table
as
Return
(
Select m.DMI_Loan_Number,
m.Old_Loan_Number,
First_Principal_Balance =
Case
When i.Part_Flag = 'Y'
Then i.Part_Ret_Pct * m.First_Principal_Balance
Else m.First_Principal_Balance
End,
Rate_Flag =
Case
When ARM_Index_Code_1 is null Then 'F'
Else 'A'
End,
Next_Interest_Change_Year =
Case
When s.NxtIRCalcDt is null Then 0
When s.NxtIRCalcDt = 0 Then 0
Else year(s.NxtIRCalcDt)
End,
Interest_Only_Loan_Indicator =
Case
When Interest_Only_Loan_Indicator is null Then 'N'
Else 'Y'
End,
ARM_Plan_ID =
Case
When ARM_Plan_ID is null Then '0'
Else ARM_Plan_ID
End,
m.Investor_ID,
m.Property_Alpha_State_Code
From dbo.mtg_dmimonthly m inner join dbo.mtg_investor i
on (m.investor_id = i.investor_id and m.category_code = i.category_code)
left outer join dbo.mtg_dmimonthlysupp s
on (m.dmi_loan_number = s.dmi_loan_number and m.yearmonth = s.yearmonth)
Where
i.svcg_ret_flag = 'y'
and ((i.part_flag = 'n' and sold_flag = 'n')
or (i.part_flag = 'y' and sold_flag = 'y'))
and i.part_release_pct <> 1
and m.YearMonth = @YearMonth
)

GO



Back to Macros

Back to Resume

Back to About Me