Link to home
Get AccessLog in
Avatar of Isaiah Melendez
Isaiah Melendez

asked on

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

Open in new window

Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

I think you overcomplicated the problem. To calculate current year or previous year is much simpler:
SELECT CAST(CONCAT(YEAR(NOW())-1, '-01-01') AS DATE), CAST(CONCAT(YEAR(NOW())-1, '-12-31') AS DATE)

Open in new window

So your code should look like this:
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 ro.repair_order_status_id in (5, 6)
    and posted_date between CAST(CONCAT(YEAR(NOW()), '-01-01') AS DATE) and CAST(CONCAT(YEAR(NOW()), '-12-31') AS DATE)
    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 CAST(CONCAT(YEAR(NOW())-1, '-01-01') AS DATE) and CAST(CONCAT(YEAR(NOW())-1, '-12-31') AS DATE)
    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

Open in new window

But it is not a good idea to left join previous year to current year because this way only those stores will be displayed in your report which have any sales in the current year. Other stores which have no sales in the current year but have sales in the previous year will be hidden in your report. So my recommendation is to left join current year to the previous year.
Avatar of Isaiah Melendez
Isaiah Melendez

ASKER

Hi, Mate,

I think there might have been a misunderstanding. What I am trying to accomplish is if when I run the query if the data in there contains date for current year of 2020-01-01 through let's say 2020-04-30, I would want the previous year to also pull that same time frame 2019-01-01 and 2019-04-30. Does that make sense?
ASKER CERTIFIED SOLUTION
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Mate, that did it. Now, if I wanted to do the same but for month over month for previous year comparing to CY month. Example: if we are in 2020-04-01 - assume month has not finished and comparing same time frame dynamically for PY but for month of April?
Hi, filter condition for the previous year same period:
SELECT CAST(CONCAT(YEAR(NOW())-1, '-', MONTH(NOW()), '-01') AS DATE), CAST(CONCAT(YEAR(NOW())-1, '-', MONTH(NOW()), '-', DAY(NOW())) AS DATE)

Open in new window

and for current year:
SELECT CAST(CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-01') AS DATE), NOW()

Open in new window