troubleshooting Question

MySQL - Year Over Year Query

Avatar of Isaiah Melendez
Isaiah Melendez asked on
MySQL ServerSQL
5 Comments1 Solution44 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros