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
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.