DROP TEMPORARY TABLE IF EXISTS tmp1_salesdata;
CREATE TEMPORARY TABLE tmp1_salesdata LIKE salesdata;
INSERT INTO tmp1_salesdata (clarion_date, his_key, item_desc, his_loctn, ty_retail, ly_retail, ty_cost, ly_cost, ty_units, ly_units)
SELECT * FROM (
SELECT this_year.clarion_date,
this_year.his_key,
this_year.item_desc,
this_year.his_loctn,
ROUND(COALESCE(this_year.his_salretl, 0.0), 2) AS ty_retail,
ROUND(COALESCE(last_year.his_salretl, 0.0), 2) AS ly_retail,
ROUND(COALESCE(this_year.his_salcost, 0.0), 2) AS ty_cost,
ROUND(COALESCE(last_year.his_salcost, 0.0), 2) AS ly_cost,
ROUND(COALESCE(this_year.his_salunit, 0.0), 2) AS ty_units,
COALESCE(last_year.his_salunit, 0) AS ly_units
FROM ( SELECT weekly_periods.clarion_date,
weekly_periods.fiscalyear,
weekly_periods.fiscalperiod,
syinvhis.his_key,
iteminfo.item_desc,
syinvhis.his_loctn,
SUM(syinvhis.his_salunit) AS his_salunit,
SUM(syinvhis.his_salretl) AS his_salretl,
SUM(syinvhis.his_salcost) AS his_salcost
FROM weekly_periods
INNER JOIN syinvhis
ON syinvhis.his_tdate = weekly_periods.clarion_date
AND syinvhis.his_type = 'DP'
AND syinvhis.his_loctn IN
('01', '02', '03', '04', '05')
AND syinvhis.his_period = 'W'
AND syinvhis.his_service IN (', 'R')
INNER JOIN (SELECT inventory.item_key,
departments.item_desc
FROM (SELECT DISTINCT ih_dept AS item_key
FROM syinvhdr
WHERE ih_sbclass = 'HDG'
AND LENGTH(ih_dept) > 0)
AS inventory
LEFT JOIN
( SELECT trim(
substr(pm_element, 4, 3))
AS item_key,
pm_str_var AS item_desc
FROM syparamt
WHERE pm_element LIKE 'DEP%'
ORDER BY pm_element) AS departments
ON inventory.item_key =
departments.item_key) AS iteminfo
ON iteminfo.item_key = syinvhis.his_key
WHERE (weekly_periods.fiscalyear > 2011
OR(weekly_periods.fiscalyear = 2011
AND weekly_periods.fiscalperiod >= 1))
AND(weekly_periods.fiscalyear < 2011
OR(weekly_periods.fiscalyear = 2011
AND weekly_periods.fiscalperiod <= 10))
GROUP BY syinvhis.his_key, iteminfo.item_desc, syinvhis.his_loctn)
AS this_year
LEFT JOIN
( SELECT weekly_periods.clarion_date,
weekly_periods.fiscalyear,
weekly_periods.fiscalperiod,
syinvhis.his_key,
syinvhis.his_loctn,
SUM(syinvhis.his_salunit) AS his_salunit,
SUM(syinvhis.his_salretl) AS his_salretl,
SUM(syinvhis.his_salcost) AS his_salcost
FROM weekly_periods
INNER JOIN syinvhis
ON syinvhis.his_tdate = weekly_periods.clarion_date
AND syinvhis.his_type = 'DP'
AND syinvhis.his_loctn IN
('01', '02', '03', '04', '05')
AND syinvhis.his_period = 'W'
AND syinvhis.his_service IN (', 'R')
INNER JOIN (SELECT DISTINCT ih_dept AS item_key
FROM syinvhdr
WHERE ih_sbclass = 'HDG') AS iteminfo
ON iteminfo.item_key = syinvhis.his_key
WHERE (weekly_periods.fiscalyear > 2010
OR(weekly_periods.fiscalyear = 2010
AND weekly_periods.fiscalperiod >= 1))
AND(weekly_periods.fiscalyear < 2010
OR(weekly_periods.fiscalyear = 2010
AND weekly_periods.fiscalperiod <= 10))
GROUP BY syinvhis.his_key, syinvhis.his_loctn) AS last_year
ON this_year.his_loctn = last_year.his_loctn
AND this_year.his_key = last_year.his_key
ORDER BY this_year.his_key, this_year.his_loctn, this_year.clarion_date) AS salesdata;
DROP TEMPORARY TABLE IF EXISTS tmp2_salesdata;
CREATE TEMPORARY TABLE tmp2_salesdata SELECT * FROM tmp1_salesdata;
SELECT grp.his_key
#, grp.grp_Sales
, loc.item_desc
, loc.his_loctn
, loc.ty_retail
, loc.ty_cost
, loc.ty_units
, loc.ly_retail
, loc.ly_cost
, loc.ly_units
FROM ( SELECT his_key
, SUM(ty_retail) AS grp_sales
FROM tmp1_salesdata
GROUP
BY his_key ) AS grp
INNER
JOIN ( SELECT his_key
, his_loctn
, item_desc
, SUM(ty_retail) AS ty_retail
, SUM(ty_cost) AS ty_cost
, SUM(ly_retail) AS ly_retail
, SUM(ly_cost) AS ly_cost
, SUM(ty_units) AS ty_units
, SUM(ly_units) AS ly_units
FROM tmp2_salesdata
GROUP
BY his_key
, his_loctn ) AS loc
ON loc.his_key = grp.his_key
ORDER
BY grp.grp_sales DESC
, loc.his_loctn
