faster join

-- slower 91.56%
alter PROCEDURE x_Financing_ExceedsDueDate
@NumberOfDays INT
AS
DECLARE @DayToday DATETIME
SET @DayToday = convert(DATETIME,CONvERT(vARCHAR,GETDATE(),112))
SELECT
X.InvoiceNo, X.PmtStructureNo, Fullname = COALESCE(i.Fullname, m.FUllname),D.PayImmediately,
DaysExceed = DATEDIFF(DAY, BillStatementDate, @DayToday)
FROM AccountInstallmentFinancingDetailFinal DINNER JOIN ( SELECT InvoiceNo, PmtStructureNo, PSO = MAX(PaymentSortOrder) FROM AccountInstallmentFinancingDetailFinal GROUP BY InvoiceNo, PmtStructureNo ) XON X.InvoiceNo = D.InvoiceNoAND X.PmtStructureNo = D.PmtStructureNoAND X.PSO = D.PaymentSortOrder
LEFT JOIN AccountInstallment ION I.InvoiceNo = D.InvoiceNo
LEFT JOIN Financing_AssumptionOfMortgage mON m.AoMNo = d.InvoiceNo
WHERE D.BillStatementDate IS NOT NULL AND @DayToday >= BillStatementDate + @NumberOfDays AND PayImmediately > 0

-- faster 8.44%

ALTER PROCEDURE Financing_ExceedsDueDate
@NumberOfDays INT
AS
DECLARE @DayToday DATETIME
SET @DayToday = convert(DATETIME,CONvERT(vARCHAR,GETDATE(),112))
SELECT
X.InvoiceNo, X.PmtStructureNo, A.Fullname,D.PayImmediately,
DaysExceed = DATEDIFF(DAY, BillStatementDate, @DayToday)
FROM AccountInstallmentFinancingDetailFinal DINNER JOIN ( SELECT InvoiceNo, PmtStructureNo, PSO = MAX(PaymentSortOrder) FROM AccountInstallmentFinancingDetailFinal GROUP BY InvoiceNo, PmtStructureNo ) XON X.InvoiceNo = D.InvoiceNoAND X.PmtStructureNo = D.PmtStructureNoAND X.PSO = D.PaymentSortOrder
INNER JOIN ( SELECT InvoiceNo, Fullname FROM AccountInstallment UNION SELECT AoMNo, Fullname FROM Financing_AssumptionOfMortgage ) A
ON X.InvoiceNo = A.InvoiceNo
WHERE D.BillStatementDate IS NOT NULL AND @DayToday >= BillStatementDate + @NumberOfDays AND PayImmediately > 0