I have a complex SQL query that involves a LEFT JOIN with multiple subqueries and conditions. The query seems quite convoluted and I’m wondering if there is a better, more efficient way to write it. Here’s the code:

LEFT JOIN ODS.YARDI_COMMAMENDMENTS ALU ON
    A.HTENANT = ALU.HTENANT AND
    A.DTSTART <= ALU.DTSTART AND
    ALU.ITYPE NOT IN (4, 6, 9, 11) AND
    ALU.ISTATUS IN (1, 2) AND
    ALU.HMY IN (SELECT UX2.HAMENDMENT FROM ODS.YARDI_UNITXREF UX2 WHERE
        U.HMY = UX2.HUNIT AND
        ALU.HMY = UX2.HAMENDMENT) AND
    COALESCE(ALU.DTMOVEOUT, ALU.DTEND, CJ.VAR_DTDATE) = (
        SELECT MAX(ALU_DATE)
        FROM (
            SELECT COALESCE(A3.DTMOVEOUT, A3.DTEND, CJ.VAR_DTDATE) AS ALU_DATE
            FROM ODS.YARDI_COMMAMENDMENTS A3
            WHERE
                A.HTENANT = A3.HTENANT AND
                A.DTSTART <= A3.DTSTART AND
                A3.ITYPE NOT IN (4, 6, 9, 11) AND 
                A3.ISTATUS IN (1, 2) AND
                A3.HMY IN (SELECT UX3.HAMENDMENT FROM ODS.YARDI_UNITXREF UX3 WHERE
                    U.HMY = UX3.HUNIT AND
                    A3.HMY = UX3.HAMENDMENT)
        ) ALU_SUB
    ) AND
    ALU.HMY = (SELECT 
        MAX(A4.HMY)
        FROM ODS.YARDI_COMMAMENDMENTS A4
        WHERE
            A.HTENANT = A4.HTENANT AND
            A.DTSTART <= A4.DTSTART AND
            COALESCE(ALU.DTMOVEOUT, ALU.DTEND, CJ.VAR_DTDATE) = COALESCE(A4.DTMOVEOUT, A4.DTEND, VAR_DTDATE) AND
            A4.ITYPE NOT IN (4, 6, 9, 11) AND
            A4.ISTATUS IN (1, 2) AND
            A4.HMY IN (SELECT UX4.HAMENDMENT FROM ODS.YARDI_UNITXREF UX4 WHERE
                U.HMY = UX4.HUNIT AND
                A4.HMY = UX4.HAMENDMENT))

I have considered using Common Table Expressions (CTEs), but doing so would require rewriting a substantial amount of code since it involves referencing columns outside the CTEs.

Any feedback is appreciated I don’t expect someone to provide any code, just any form of suggestion will be very helpful.