Hi, I'm a beginner in writing SQL, so hoping to use this community to learn and eventually self-serve and teach 
Any help is much appreciated! I mainly need help with query 2, which is taking the longest. Maybe there are some joins or columns that are not necessary for table 3, which is what I am hoping to clean up.
There are 3 queries that run.
- Temporary table that runs fairly quickly
- Temporary table that take 20-40 minutes. (Trying to reduce the run time for this)
- Query that uses table 1 and 2 to produce this table. I use the data from this table as my data set.
Here is query 1 (I Don't need to make any changes to this query)
CREATE VOLATILE TABLE SALES_LINE_BILL_WT AS
(
SELECT
FSR.SALES_ORDER_NUM,
FSR.SALES_ORDER_LINE_NUM,
FSR.TENANT_ORG_ID,
sol.ORIG_SHPG_MTHD_ID,
sol.order_placed_dt,
MAX(CAST(NVL(SOL.BILL_WT,0) AS DECIMAL(35,4))) AS BILL_WT
FROM
WW_GEC_VM.FIN_SHPD_SALES_REV FSR
INNER JOIN
WW_GEC_VM.SALES_ORDER_LINE SOL
ON SOL.SALES_ORDER_NUM = FSR.SALES_ORDER_NUM
AND SOL.SALES_ORDER_LINE_NUM = FSR.SALES_ORDER_LINE_NUM
AND SOL.TENANT_ORG_ID = FSR.TENANT_ORG_ID
WHERE
FSR.TENANT_ORG_ID = 4571
AND FSR.SVC_ID IN (0,14,8)
AND FSR.REV_TYPE_ID = 1
AND FSR.PROD_CLASS_TYPE_ID NOT IN (30,47,56)
AND FSR.EVENT_DT BETWEEN '2016-04-01' AND '2016-04-18'
AND SOL.ORDER_PLACED_DT BETWEEN CAST('2016-04-01' AS DATE)-100 AND '2016-04-18'
GROUP BY 1,2,3,4,5
)WITH DATA
PRIMARY INDEX(SALES_ORDER_NUM, SALES_ORDER_LINE_NUM, TENANT_ORG_ID)
ON COMMIT PRESERVE ROWS;
COLLECT STATS ON SALES_LINE_BILL_WT INDEX (SALES_ORDER_NUM, SALES_ORDER_LINE_NUM, TENANT_ORG_ID);
Table 2 (This is the table I would like help reducing):
CREATE VOLATILE TABLE SHPD_DTL AS
(
SELECT
FSSR.SALES_ORDER_NUM,
FSSR.SALES_ORDER_LINE_NUM,
FSSR.EVENT_DT,
FSSR.XCHNG_IND,
FSSR.SHPG_MTHD_ID,
FSSR.SVC_ID,
FSSR.CATLG_ITEM_ID,
FSSR.SHPG_NODE_ORG_ID,
FSSR.TENANT_ORG_ID,
SPS.CARRIER_SVC_CD,
SPS.CARRIER_ORG_ID,
FSSR.SHPMNT_NUM,
SUM(CAST(FSSR.SHPD_QTY AS DECIMAL(35,4))) AS SHIPPED_QUANTITY ,
SUM(CAST(FSSR.UNIT_PRICE AS DECIMAL(35,4))*CAST(FSSR.SHPD_QTY AS DECIMAL(35,4))) AS SHIPPED_SALES,
SUM(CAST(SPSL.POST_EST_SHPG_COST AS DECIMAL(35,4))*CAST(FSSR.SHPD_QTY AS DECIMAL(35,4))) AS EST_SHIPPING_COST_MDE,
SUM( CAST(FSSR.POS_COST AS DECIMAL(35,4)) * CAST(FSSR.SHPD_QTY AS DECIMAL(35,4)) ) AS SHIPPED_COST,
SUM(DTL.CHRG_AMT) SHIP_CHARGE
FROM
WW_GEC_VM.FIN_SHPD_SALES_REV FSSR
INNER JOIN WW_GEC_VM.SALES_PO_SHPMNT SPS
ON FSSR.TENANT_ORG_ID = SPS.TENANT_ORG_ID
AND FSSR.SHPMNT_NUM = SPS.SHPMNT_NUM
AND FSSR.PO_NUM = SPS.PO_NUM
AND FSSR.SALES_ORDER_NUM = SPS.SALES_ORDER_NUM
INNER JOIN WW_GEC_VM.SALES_PO_LINE SPOL
ON FSSR.TENANT_ORG_ID = SPOL.TENANT_ORG_ID
AND FSSR.SALES_ORDER_NUM = SPOL.SALES_ORDER_NUM
AND FSSR.PO_NUM = SPOL.PO_NUM
AND FSSR.PO_LINE = SPOL.PO_LINE_NUM
INNER JOIN WW_GEC_VM.SALES_PO_SHPMNT_LINE SPSL
ON FSSR.TENANT_ORG_ID = SPSL.TENANT_ORG_ID
AND FSSR.SALES_ORDER_NUM = SPSL.SALES_ORDER_NUM
AND SPOL.PO_KEY = SPSL.PO_KEY
AND SPOL.PO_LINE_KEY = SPSL.PO_LINE_KEY
LEFT JOIN
WW_GEC_VM.FIN_REV_TYPE RT
ON FSSR.REV_TYPE_ID = RT.REV_TYPE_ID
LEFT JOIN
WW_GEC_VM.FIN_SHPD_SALES_REV_DTL DTL
ON (FSSR.SALES_ORDER_NUM = DTL.SALES_ORDER_NUM
AND FSSR.SALES_ORDER_LINE_NUM = DTL.SALES_ORDER_LINE_NUM
AND FSSR.TENANT_ORG_ID = DTL.TENANT_ORG_ID
AND FSSR.REV_SEQ = DTL.REV_SEQ
AND FSSR.EVENT_DT = DTL.EVENT_DT)
AND DTL.CHRG_NM='SHIPPING_CHARGE'
WHERE
FSSR.TENANT_ORG_ID=4571
AND FSSR.SVC_ID IN (0,14,8)
AND RT.REV_TYPE_DESC ='SHIP_SALES'
AND FSSR.PROD_CLASS_TYPE_ID NOT IN (30,47,56)
AND FSSR.EVENT_DT >= '2016-04-01'
AND FSSR.EVENT_DT <'2016-04-18'
AND SPSL.SRC_CRE_DT BETWEEN CAST('2016-04-01' AS DATE)-20 AND '2016-04-18'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12
)WITH DATA
PRIMARY INDEX(SALES_ORDER_NUM, TENANT_ORG_ID)
ON COMMIT PRESERVE ROWS;
COLLECT STATS ON SHPD_DTL INDEX (SALES_ORDER_NUM, TENANT_ORG_ID);
Table 3 (Final Output, don't need help here )
ELECT
CWY.WM_YR_DESC AS WM_YEAR,
CLM.MONTH_DESC AS CAL_MONTH,
RH.DEPT_NM AS DEPARTMENT,
OSN.ORG_NM AS DIST_NAME,
SD.SVC_ID AS SERVICE_ID,
OC.ORG_NM AS CARRIER_NAME,
SCM.CARRIER_MTHD_DESC AS ACTUAL_CARRIER_METHOD,
SD.SHPG_MTHD_ID AS SHIPPING_METHOD_ID,
SUM(SD.SHIPPED_QUANTITY) AS SHIPPED_QUANTITY,
SUM(SD.SHIPPED_SALES) AS SHIPPED_SALES,
SUM(SD.EST_SHIPPING_COST_MDE) AS EST_SHIPPING_COST_MDE,
SUM(SD.EST_SHIPPING_COST_MDE) AS EST_SHIPPING_COST_MDE,
SUM(SOL.BILL_WT*SD.SHIPPED_QUANTITY) AS BILLABLE_WEIGHT,
COUNT(DISTINCT SD.SHPMNT_NUM) AS SHIPPED_PACKAGES
FROM
SHPD_DTL SD
INNER JOIN
SALES_LINE_BILL_WT SOL
ON SOL.SALES_ORDER_NUM = SD.SALES_ORDER_NUM
AND SOL.SALES_ORDER_LINE_NUM = SD.SALES_ORDER_LINE_NUM
AND SOL.TENANT_ORG_ID = SD.TENANT_ORG_ID
LEFT JOIN
WW_GEC_VM.PROD_RPT_HRCHY PRH
ON SD.CATLG_ITEM_ID =PRH.CATLG_ITEM_ID
LEFT JOIN
WW_GEC_VM.RPT_HRCHY RH
ON PRH.RPT_HRCHY_ID = RH.RPT_HRCHY_ID
LEFT JOIN
WW_GEC_VM.CAL_DT CDT
ON SD.EVENT_DT = CDT.CAL_DT
LEFT JOIN
WW_GEC_VM.CAL_WM_WK CWW
ON CDT.WM_WK_ID = CWW.WM_WK_ID
LEFT JOIN
WW_GEC_VM.CAL_MONTH CLM
ON CLM.MONTH_ID = CDT.CAL_MONTH_ID
LEFT JOIN
WW_GEC_VM.CAL_WM_YR CWY
ON CWY.NEXT_WM_YR_ID = CDT.WM_YR_ID
LEFT JOIN
WW_GEC_VM.ORG_SHIP_NODE OSN
ON SD.SHPG_NODE_ORG_ID = OSN.ORG_ID
LEFT JOIN
WW_GEC_NZ_VM.ORG_DSTRBTR_GRP ODG
ON SD.SHPG_NODE_ORG_ID = ODG.DSTRBTR_ORG_ID
LEFT JOIN
WW_GEC_VM.ORG_CARRIER OC
ON SD.CARRIER_ORG_ID = OC.ORG_ID
LEFT JOIN
WW_GEC_VM.SHPG_MTHD SM
ON SD.SHPG_MTHD_ID = SM.SHPG_MTHD_ID
LEFT JOIN
WW_GEC_VM.SHPG_CARRIER_MTHD SCM
ON CAST(SD.CARRIER_SVC_CD AS DECIMAL(18,0))=SCM.CARRIER_MTHD_ID
WHERE
PRH.CURR_IND=1
AND RH.CURR_IND=1
AND PRH.CATLG_ITEM_ID <>-999
AND SD.SHPG_MTHD_ID NOT IN ('12')
GROUP BY
WM_YEAR,
CAL_MONTH,
DEPARTMENT,
DIST_NAME,
SERVICE_ID,
CARRIER_NAME,
ACTUAL_CARRIER_METHOD,
SHIPPING_METHOD_ID;
Any help is much appreciated, Thanks!!