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.
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``` Thanks in advance!