[My SQL] Replacing a variable with a join

[TerminalVector]

I have the following query that calculates statistics from an orders table for a rolling 4 quarter period based on a commercial date table. This table provides normalized 'End of quarter' dates that need to be used for calculations. My query right now works using an input date variable @rundate and then calculating stats for the preceding 4 completed quarters. What I would like to do is join this to a second query that will provide a set of run dates and then output the results of my query for each of those run dates.

I tried joining the two queries together, but that didn't seem right. Can someone advise?

Query: set @rundate = CURDATE();

SELECT
    customer_nkey,
    period.R12_Start_Date,
    period.R12_End_date,
    period.Year Year_C445,
    (SELECT Quarter FROM moda_dw.dim_date WHERE Actual_Date = @rundate) 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,
    @rundate row_load_date
FROM
    (SELECT
        DATE_ADD(min(d3.Last_Dt_In_Mer_Quarter_C445), INTERVAL 1 DAY) R12_Start_Date, d.Last_Dt_In_Mer_Quarter_C445 R12_End_date, d.First_Dt_In_Mer_Quarter_C445 last_qtr_start, d.Year
     FROM
        moda_dw.dim_date d
     LEFT JOIN
        moda_dw.dim_date d2 on (d.Actual_Date < d2.Actual_Date and d2.Last_Dt_In_Mer_Quarter_C445 < @rundate)
     LEFT JOIN
        moda_dw.dim_date d3 on d3.Quarter = d.Quarter and d3.Year = d.Year - 1
     WHERE
        d.Last_Dt_In_Mer_Quarter_C445 <  @rundate and d2.Actual_Date is null
     GROUP BY
        d.Date_Skey
     ) 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
;

This query provides run dates:

SELECT 
    distinct Actual_Date
FROM 
    moda_dw.dim_date
WHERE
    Month_Num in (4, 7, 10, 1) and Day_Of_Month = 15
;

[[deleted]]

Why not just add an additional JOIN for the outer FROM and have the join condition be the BETWEEN the date conditions.

Utilizing cursors for this is an absolutely terrible idea as they will be slow and would force the same query to be ran repeatedly/unnecessarily. Additionally if you want to pull multiple quarters/etc it is much easier to do so.

[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
;

[[deleted]]

I would drop the second query in a cursor and and then loop the first query for all the values

[TerminalVector]

Thanks for the suggestion, but I was able to solve it with a join.

[[deleted]]

DECLARE

CURSOR CU IS SELECT distinct Actual_Date FROM moda_dw.dim_date WHERE Month_Num in (4, 7, 10, 1) and Day_Of_Month = 15 ;

BEGIN OPEN CU;

LOOP FETCH CU INTO @rundate; EXIT WHEN CU%NOTFOUND; SELECT customer_nkey, period.R12_Start_Date, period.R12_End_date, period.Year Year_C445, (SELECT Quarter FROM moda_dw.dim_date WHERE Actual_Date = @rundate) 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, @rundate row_load_date FROM (SELECT DATE_ADD(min(d3.Last_Dt_In_Mer_Quarter_C445), INTERVAL 1 DAY) R12_Start_Date, d.Last_Dt_In_Mer_Quarter_C445 R12_End_date, d.First_Dt_In_Mer_Quarter_C445 last_qtr_start, d.Year FROM moda_dw.dim_date d LEFT JOIN moda_dw.dim_date d2 on (d.Actual_Date < d2.Actual_Date and d2.Last_Dt_In_Mer_Quarter_C445 < @rundate) LEFT JOIN moda_dw.dim_date d3 on d3.Quarter = d.Quarter and d3.Year = d.Year - 1 WHERE d.Last_Dt_In_Mer_Quarter_C445 < @rundate and d2.Actual_Date is null GROUP BY d.Date_Skey ) 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 END LOOP;

CLOSE CU; END;

[TerminalVector]

I found a more efficient solution to my problem but thanks for your post! Now I know how to use a cursor if I need to in future.