Hi, I'm new here and hoping to find some help.
My query currently retrieves 'last_thirteen_wk_sales' by 'ITEM_ID' and 'LOCN_NBR'.
I want to change it so it will SUM 'last_thirteen_wk_ sales' by 'ITEM_ID' and 'SRV_DC LOCN_NBR'.
LOCN_NBR AND SRV_DC LOCN_NBR is a "many to one" relationship so the result that I am looking will return far fewer records that what it does currently summing on LOCN_NBR.
I'm thinking this involves the 'DISTINCT' or 'UNIQUE' keyword but I'm not clear on the syntax. Thanks in advance...
SELECT a.SYS_DATE, a.KSN_ID, a.ITEM_ID, a.DVSN_NBR, a.CAN_CARR_MDL_ID, a.NETWORK_DISTRB_CD, a.KM_DAY70_CHK_STR_QTY, a.LOCN_NBR, a.SERV_DC_LOCN_NBR, b.last_thirteen_wk_sales from (SELECT DATE as SYS_DATE, T1.KSN_ID as KSN_ID, T1.ITEM_ID as ITEM_ID, T1.DVSN_NBR as DVSN_NBR, T2.CAN_CARR_MDL_ID as CAN_CARR_MDL_ID, T2.NETWORK_DISTRB_CD as NETWORK_DISTRB_CD, T3.KM_DAY70_CHK_STR_QTY as KM_DAY70_CHK_STR_QTY, T4.LOCN_NBR as LOCN_NBR, T4.SERV_DC_LOCN_NBR as SERV_DC_LOCN_NBR from Dataview.ksn T1, Dataview.item T2, Dataview.Item_store_count_c T3, Dataview.item_locn_replen T4 --oi_merchT3, where T1.KSN_ID IN ( 2981558 ) AND T1.ITEM_ID = T2.ITEM_ID AND T1.ITEM_ID = T3.ITEM_ID AND T1.ITEM_ID = T4.ITEM_ID ) a --JOIN a and b Join ( SELECT J1.KSN_ID as KSN_ID, J1.LOCN_NBR as LOCN_NBR, Sum (SELL_AMT) last_thirteen_wk_sales FROM Dataview.SALES_WK_CALNDR_C J1 WHERE date - CALNDR_WK_END_DT<= 91 GROUP BY 1,2) b ON (a.KSN_ID = b.KSN_ID and a.LOCN_NBR = b.LOCN_NBR ) group by 1,2,3,4,5,6,7,8,9,10 order by 3 ASC;