MySQL - Year Over Year Query

Hello, experts,

I am working on a query to get Year over Year data comparison from current year to prior year.

For example, if current year is Jan 1st - now, I would calc that dynamically in my query but also bring in the previous year data for the same time frame (does that make sense?).

Here is my projection query that brings in current year and prior year but the data is dynamic yet static if that makes sense. CY is focusing on all of YTD while PY is doing entire PY.

How would I match PY to mimic what CY is doing essentially giving me all the data that I am needing from PY to do the comparison.

I would appreciate your help in advance.

select  current_4wk_sales.store_uin as 'store_uin',
        current_4wk_sales.store_no as 'store_no',
        current_4wk_sales.store_name as 'store_name',
        current_4wk_sales.zee_name as 'franchisee',
        current_4wk_sales.marketing_source as 'region',
        current_4wk_sales.cy_projections as 'cy_projections',
        prev_wk_sales.prev_year_sales as 'prev_year_sales',
        round(((current_4wk_sales.cy_projections - prev_wk_sales.prev_year_sales) / prev_wk_sales.prev_year_sales), 2) * 100 as 'percent_chng'
from (
    select store.store_uin,
           store.store_no,
           store.store_name,
           store.zee_name,
           store.marketing_source,
           round((((sum(ro.total_sales - ro.taxes) / 100)) / (DAYOFYEAR(current_date() - INTERVAL 2 DAY))) * 365, 2) as 'cy_projections'

    from cba_data_warehouse.raw_tm_data ro
    join cba_data_warehouse.storeinfo store on ro.store_no = store.store_no
    where posted_date between MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) and LAST_DAY(DATE_ADD(NOW(), INTERVAL 12-MONTH(NOW()) MONTH)) and ro.repair_order_status_id in (5,6)
    group by store.store_uin, store.store_no, store.store_name
    order by store.store_no asc
) as current_4wk_sales
left join (
    select
        store.store_uin,
        store.store_no,
        store.store_name,
        store.zee_name,
        store.marketing_source,
        round(sum(ro.total_sales - ro.taxes) / 100,2) as prev_year_sales

    from cba_data_warehouse.raw_tm_data ro
    join cba_data_warehouse.storeinfo store on ro.store_no = store.store_no
    where ro.repair_order_status_id in (5, 6)
    and ro.posted_date between MAKEDATE(year(now() - INTERVAL 1 YEAR),1) and DATE(CURDATE()- INTERVAL DAYOFYEAR(CURDATE()) DAY)
    group by store.store_uin, store.store_no, store.store_name
    order by store.store_no asc
) prev_wk_sales on prev_wk_sales.store_uin = current_4wk_sales.store_uin
                and prev_wk_sales.store_no = current_4wk_sales.store_no
                and prev_wk_sales.store_name = current_4wk_sales.store_name

Select allOpen in new window