Hi everybody,

Need the help of a Teradata expert on how the SQL below can be optimized. It's currently consuming a lot of CPU resources and using a lot of spool space.

Can anybody help?
SELECT tb1.store_id
,tb1.upc_id
,'10/10/2006' (date, format 'mm/dd/yyyy')
,(SUM(tb1.base_qty) - MIN(tb1.base_qty) - MAX(tb1.base_qty)) / (COUNT(tb1.txn_dt) - 2)
,(SUM(tb1.base_amt) - MIN(tb1.base_amt) - MAX(tb1.base_amt)) / (COUNT(tb1.txn_dt) - 2)
FROM orca_db.txn_dtl tb1
GROUP BY 1,2,3
HAVING COUNT(tb1.txn_dt) >= 6;

Thanks
Rick

without knowing the full details of what you're wanting to do - I suggest you run the query a number of times, removing one line at a time.
Narrow down the line(s) that are causing the issue then perhaps we can optomize those.

can u please help me in optimizing the query?

SELECT (V001.BTN_NPA_CD||V001.BTN_NXX_CD||V001.BTN_LINE_N BR) BTN,MAX(V001.DW_LOAD_DT) DW_LOAD_DT FROM

VBTF001_TELCO_REVENUE B,VCTD700 A
WHERE A.BTN_NPA_CD = B.BTN_NPA_CD

AND A.BTN_NXX_CD = B.BTN_NXX_CD

AND A.BTN_LINE_NBR = B.BTN_LINE_NBR

AND A.REPTD_SERV = ''

AND A.RPT_CAT = 'CR'

AND A.DISP LIKE ANY ('03%','04%','05%','07%',

'08%','09%','11%','12%') group by 1

As B table has huge records, so it was taking more than allocate space