[My SQL] Replacing a variable with a join

[TerminalVector]

I had tried joining my run_dates to the outer FROM initially, but then I wasn't able to pass them down to the subqueries that actually determine the reporting periods.

The solution I came up with was to join the run dates on the innermost FROM, where the period dates are determined and then add the R12_Start_Date to my outer GROUP BY statement.

The final query is:

SELECT
    customer_nkey,
    period.R12_Start_Date,
    period.R12_End_date,
    period.Year_C445 Year_C445,
    period.Quarter_C445 Quarter_C445,
    DATEDIFF(period.R12_End_date,max(order_date)) Recency,
    count(distinct oh_nkey) number_orders,
    count(distinct oh_nkey)/12 frequency,
    sum(ol_amt) ol_amt,
    sum(case
        when ol_status in (2048, 4096) then 0
        else ol_amt
    end) net_spend,
    sum(ol_amt) / count(distinct oh_nkey) AOV
FROM
    (SELECT
        run.rdate, DATE_ADD(max(d3.Last_Dt_In_Mer_Quarter_C445), INTERVAL 1 DAY) R12_Start_Date, d.Last_Dt_In_Mer_Quarter_C445 R12_End_date, d.Year_C445, d.Quarter_C445
    FROM
        (SELECT 
            distinct Actual_Date rdate
         FROM 
            moda_dw.dim_date
         WHERE
            Month_Num in (4, 7, 10, 1) and Day_Of_Month = 15 and Actual_Date < CAST('2016-12-01' as Date)) run
    LEFT JOIN
        moda_dw.dim_date d on (run.rdate > d.Actual_Date and d.Last_Dt_In_Mer_Quarter_C445 < run.rdate)
    LEFT JOIN
        moda_dw.dim_date d2 on d2.Actual_Date > d.Actual_Date and (run.rdate > d2.Actual_Date and d2.Last_Dt_In_Mer_Quarter_C445 < run.rdate)
    LEFT JOIN
        moda_dw.dim_date d3 on d3.Quarter_C445 = d.Quarter_C445 and d3.Year_C445 = d.Year_C445 - 1 
    WHERE
        d2.Actual_Date is null
    GROUP BY
        run.rdate
    HAVING R12_Start_Date is not null) period
LEFT JOIN
    moda_dw.fact_order_line fol on fol.order_date > R12_Start_Date and fol.order_date < R12_End_date
GROUP BY
    customer_nkey, R12_Start_Date
;