Hello,
I’m trying to write query to return sku items that were purchased in consecutive months over periods time (let’s say 3 yrs). Out of this list, I need to get skus that make at least 35% of total sales from that vendor.
My columns are:
VENDOR, FISCAL_YEAR, FISCAL_MONTH, SKU_ITEM, QUANTITY_SOLD, TOTAL _SALES (these are the main ones I’m concerned with, I’ve posted my script below)
Can anyone help?
I’ve already written a script to retrieve just one vendor
select
VENDOR,
FISCAL_YEAR,
FISCAL_MONTH_NO,
MARKET,
CASE WHEN MARKET IN (‘PO’, ‘SC’, ‘OC’) THEN ‘PC’ ELSE ‘EC’ END AS MARKET_TYPE,
PC_ITEM_NO,
PC_PROD_CAT,
PC_PROD_SUBCAT,
SUM(QTY_EACH) AS QUANTITY_SOLD,
SUM(EXT_GROSS_COGS) AS TOTAL_COGS,
SUM(EXT_GROSS_COGS)/ SUM(QTY_EACH) as ACOGS,
SUM(EXT_SALES) AS TOTAL_SALES,
SUM(EXT_SALES)/ SUM(QTY_EACH) as ASP
from SIXSIGMA.CIA_ALL_SALES_TREND_DATA
WHERE VENDOR = ‘MOLYCK’
GROUP BY
VENDOR,
FISCAL_YEAR,
FISCAL_MONTH_NO,
MARKET,
CASE WHEN MARKET IN (‘PO’, ‘SC’, ‘OC’) THEN ‘PC’ ELSE ‘EC’ END,
PC_ITEM_NO,
PC_PROD_CAT,
PC_PROD_SUBCAT
ORDER BY PC_ITEM_NO,FISCAL_YEAR, FISCAL_MONTH_NO