Toad World® Forums

SQL code to retrieve items in consective periods

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

when you need filter by group of records (like in your case) then use “having” operator. here is a demo that would return records with ext_sales greater than 35% against average ext_sales

I.E.

select …
from
group by …
having SUM(EXT_SALES) > AVG (EXT_SALES) * 1.35

ok, thanks. What about the filter on items in consective periods? Can you help with that?

On Fri, Feb 28, 2014 at 9:13 AM, damir.vadas_531 bounce-damirvadas_531@toadworld.com wrote:

RE: SQL code to retrieve items in consective periods

Reply by damir.vadas_531
when you need filter by group of records (like in your case) the use “having”. here is a demo that would return records with ext_sales greater than 35% against average ext_sales

I.E.

select …
from
group by …
having SUM(EXT_SALES) > AVG (EXT_SALES) * 1.35

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.

where filter will include all records one by one, while having will filter group of records (not single one by one).

if you have where and having together then where is primary filter, while having will filter on records thts meet where part.

Hope this helps.

Brg

Damir

Ok, I need to use HAVING line in code, my problem is trying to determine how to use it, I know it need to be grouped on fiscal_year, fiscal_month and sku. But I’m not sure how to write: i.e., sku must not be null in any year-month combo. I’m a medium level coder.

any year-month combo

what do you mean …???

please say in plain English (according shown columns) what is the group filter.

Don’t group by month - you want all months in one row, not in separate rows. Group by fiscal_year and sku (and vendor, market, whatever else you want but not month). Then you can filter for stuff that sold every month with something like

HAVING SUM(case when fiscal_month = 1 then qty_each end) > 0

AND SUM(case when fiscal_month = 2 then qty_each end) > 0

AND SUM(case when fiscal_month = 3 then qty_each end) > 0

Sorry, I meant to say, in order to meet the criteria, sku items must have been purchased in every month/year consecutively, i.e., from 1/1/12 - 1/31/14, there must be a sku number (purchase) in every month in order to meet criteria, then I’ll use the: having SUM(EXT_SALES) > AVG (EXT_SALES) * 1.35

if you want to have month and year then your grouping must be like that.

group by …, to_char(some_date,‘mm’), to_char(some_date,‘yyyy’)

Thanks for your input, it has been very helpful