SELECT c.[Prod Dt], c.[TotSum],DeliqCount.CNT, DeliqCount.TOTSUM as DeliqSum,D30.TOTSUM as Deliquent30, NULLIF(d60.TOTSUM,0) as Deliquent60, d90.TOTSUM as Deliquent90, OneToFourOwn.TOTSUM OneToFourOwn, OneToFourNown.TOTSUM OneToFourNown, MultiFamily.TOTSUM MultiFamily, COMMERCIAL.TOTSUM Commercial, ConstructionAndLD.TOTSUM ConstructionAndLand, Business.TOTSUM Business, Consumer.TOTSUM Consumer, PurchasesParticpation.TOTSUM PurchasesParticipation
FROM (
(
SELECT mlr.[Prod Dt], SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt], [Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') OR
[Curr Due Dt] IS NULL THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7)) WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt],
[Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined
WHERE
[Prod Dt] >= DATEADD(M,-4,GETDATE())) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr]
AND ddays.[Prod Dt] = mlr.[Prod Dt]
WHERE
mlr.[Prod Dt] >= DATEADD(M,-4,GETDATE()) AND
(((MLR.[Investor Total Pct Sold] <> 1 OR MLR.[Investor Total Pct Sold] IS NULL) AND (MLR.[Account Status Cd] = 'ACT') AND (DDAYS.dd < 31)) OR
((MLR.[Investor Total Pct Sold] <> 1 OR MLR.[Investor Total Pct Sold] IS NULL) AND (MLR.[Account Status Cd] = 'CO') AND (DDAYS.dd < 31) AND (MLR.[Trial Bal] <> 0)) OR
((MLR.[Investor Total Pct Sold] <> 1 OR MLR.[Investor Total Pct Sold] IS NULL) AND (MLR.[Account Status Cd] = 'NPFM') AND (DDAYS.dd < 31) AND (MLR.[Minor Type Code] NOT IN ('CREO', 'SREO', 'MREO')) AND (MLR.[Minor Type Code] NOT IN ('CREO', 'SREO', 'MREO'))) OR
((MLR.[Investor Total Pct Sold] = 1) AND (DDAYS.dd < 31) AND (MLR.[Net Balance Plus Negative Reserve 1] <> 0)))
GROUP BY mlr.[Prod Dt]) c
Left outer join (SELECT mlr.[Prod Dt],COUNT(MLR.[Account Nbr]) AS CNT, SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
WHERE (DDAYS.dd >= 31)
GROUP BY mlr.[Prod Dt] ) DeliqCount on c.[Prod Dt] = DeliqCount.[Prod Dt]
LEFT OUTER JOIN (SELECT MLR.[Prod Dt],SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND ddays.[Prod Dt] = mlr.[Prod Dt]
WHERE (MLR.[Net Balance Plus Negative Reserve 1] <> 0) AND (DDAYS.dd >= 31) AND (DDAYS.dd <= 60)
GROUP BY mlr.[Prod Dt])D30 on c.[Prod Dt] = d30.[Prod Dt]
LEFT OUTER JOIN (SELECT MLR.[Prod Dt],SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND ddays.[Prod Dt] = mlr.[Prod Dt]
WHERE (MLR.[Net Balance Plus Negative Reserve 1] <> 0) AND (DDAYS.dd >= 61) AND (DDAYS.dd <= 90)
GROUP BY mlr.[Prod Dt])D60 on c.[Prod Dt] = D60.[Prod Dt]
LEFT OUTER JOIN (SELECT MLR.[Prod Dt],SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND ddays.[Prod Dt] = mlr.[Prod Dt]
WHERE (MLR.[Net Balance Plus Negative Reserve 1] <> 0) AND (DDAYS.dd >= 91)
GROUP BY mlr.[Prod Dt])D90 on c.[Prod Dt] = D90.[Prod Dt]
Left outer join (SELECT mlr.[Prod Dt],COUNT(MLR.[Account Nbr]) AS CNT, SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
LEFT OUTER JOIN
PropSQLCombined AS P ON MLR.[Account Nbr] = P.ACCOUNT AND mlr.[Prod Dt] = p.[Prod Dt]
WHERE (DDAYS.dd >= 31) AND mlr.[Loan Type Desc] = 'ONE TO FOUR FAMILY' AND COALESCE (NULLIF(P.[PROP1 Owner Occupied Cd],''), NULLIF(P.[PROP2 Owner Occupied Cd],''), NULLIF(P.[PROP3 Owner Occupied Cd],''), NULLIF(P.[PROP4 Owner Occupied Cd],''), NULLIF(P.[PROP5 Owner Occupied Cd],''), NULLIF(P.[PROP6 Owner Occupied Cd],'')) = 'OWN'
GROUP BY mlr.[Prod Dt] ) OneToFourOwn on c.[Prod Dt] = OneToFourOwn.[Prod Dt]
Left outer join (SELECT mlr.[Prod Dt],COUNT(MLR.[Account Nbr]) AS CNT, SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
LEFT OUTER JOIN
PropSQLCombined AS P ON MLR.[Account Nbr] = P.ACCOUNT AND mlr.[Prod Dt] = p.[Prod Dt]
WHERE (DDAYS.dd >= 31) AND mlr.[Loan Type Desc] = 'ONE TO FOUR FAMILY' AND COALESCE (NULLIF(P.[PROP1 Owner Occupied Cd],''), NULLIF(P.[PROP2 Owner Occupied Cd],''), NULLIF(P.[PROP3 Owner Occupied Cd],''), NULLIF(P.[PROP4 Owner Occupied Cd],''), NULLIF(P.[PROP5 Owner Occupied Cd],''), NULLIF(P.[PROP6 Owner Occupied Cd],'')) = 'NOWN'
GROUP BY mlr.[Prod Dt] ) OneToFourNown on c.[Prod Dt] = OneToFourNown.[Prod Dt]
Left outer join (SELECT mlr.[Prod Dt],COUNT(MLR.[Account Nbr]) AS CNT, SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
LEFT OUTER JOIN
PropSQLCombined AS P ON MLR.[Account Nbr] = P.ACCOUNT AND mlr.[Prod Dt] = p.[Prod Dt]
WHERE (DDAYS.dd >= 31) AND mlr.[Loan Type Desc] = 'MULTI FAMILY'
GROUP BY mlr.[Prod Dt] ) MultiFamily on c.[Prod Dt] = MultiFamily.[Prod Dt]
Left outer join (SELECT mlr.[Prod Dt],COUNT(MLR.[Account Nbr]) AS CNT, SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
LEFT OUTER JOIN
PropSQLCombined AS P ON MLR.[Account Nbr] = P.ACCOUNT AND mlr.[Prod Dt] = p.[Prod Dt]
WHERE (DDAYS.dd >= 31) AND mlr.[Loan Type Desc] = 'COMMERCIAL'
GROUP BY mlr.[Prod Dt] ) COMMERCIAL on c.[Prod Dt] = COMMERCIAL.[Prod Dt]
Left outer join (SELECT mlr.[Prod Dt],COUNT(MLR.[Account Nbr]) AS CNT, SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
LEFT OUTER JOIN
PropSQLCombined AS P ON MLR.[Account Nbr] = P.ACCOUNT AND mlr.[Prod Dt] = p.[Prod Dt]
WHERE (DDAYS.dd >= 31) AND mlr.[Loan Type Desc] = 'CONSTRUCTION AND LD'
GROUP BY mlr.[Prod Dt] ) ConstructionAndLD on c.[Prod Dt] = ConstructionAndLD.[Prod Dt]
Left outer join (SELECT mlr.[Prod Dt],COUNT(MLR.[Account Nbr]) AS CNT, SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
LEFT OUTER JOIN
PropSQLCombined AS P ON MLR.[Account Nbr] = P.ACCOUNT AND mlr.[Prod Dt] = p.[Prod Dt]
WHERE (DDAYS.dd >= 31) AND mlr.[Loan Type Desc] = 'BUSINESS'
GROUP BY mlr.[Prod Dt] ) Business on c.[Prod Dt] = Business.[Prod Dt]
Left outer join (SELECT mlr.[Prod Dt],COUNT(MLR.[Account Nbr]) AS CNT, SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
LEFT OUTER JOIN
PropSQLCombined AS P ON MLR.[Account Nbr] = P.ACCOUNT AND mlr.[Prod Dt] = p.[Prod Dt]
WHERE (DDAYS.dd >= 31) AND mlr.[Loan Type Desc] = 'CONSUMER'
GROUP BY mlr.[Prod Dt] ) Consumer on c.[Prod Dt] = Consumer.[Prod Dt]
LEFT OUTER JOIN(
SELECT mlr.[Prod Dt],SUM(MLR.[Net Balance Plus Negative Reserve 1]) AS TOTSUM
FROM MonthlyLoanReportCombined AS MLR LEFT OUTER JOIN
(SELECT [Prod Dt],[Account Nbr], CASE WHEN [Account Status Cd] IN ('CLS', 'APPR', 'ORIG') THEN 0 WHEN [Prod Dt] > [Curr Due Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Curr Due Dt], DEFAULT) AS nvarchar(7))
WHEN [Prod Dt] > [Maturity Dt] THEN CAST([dbo].[DAYS360]([Prod Dt], [Maturity Dt], DEFAULT) AS nvarchar(7)) ELSE 0 END AS dd
FROM MonthlyLoanReportCombined) AS DDAYS ON DDAYS.[Account Nbr] = MLR.[Account Nbr] AND DDAYS.[Prod Dt] = mlr.[Prod Dt]
WHERE (MLR.[Investor Total Pct Sold] = 1) AND (MLR.[Net Balance Plus Negative Reserve 1] <> 0) AND (DDAYS.dd >= 31) OR
(MLR.[Net Balance Plus Negative Reserve 1] <> 0) AND (MLR.[Purchased Org Name] IS NOT NULL) AND (DDAYS.dd >= 31)
GROUP BY mlr.[Prod Dt]) PurchasesParticpation on c.[Prod Dt] = PurchasesParticpation.[Prod Dt]
)
ORDER BY 1