QUERY HELP

Database
Enthusiast

QUERY HELP

I am new to Teradata and need help on a query.
I need to work on 4 tables with the following structure

Table Calls - Record count 10 Billion
-------------------------------------
sku store week# code# sat_sale sun_sale mon_sale tue_sale wed_sale thu_sale fri_sale sat_val sun_val mon_val tue_val wed_val

thu_val fri_val

Table Inv - Record count 10 Billion
--------------------------------------
sku store week# sat_Inv sun_Inv mon_Inv tue_Inv wed_Inv thu_Inv fri_Inv amount

Table target_drive 10 million
--------------------------------------
sku store SALESUnits SALESValue Invunits InvValue first_date last_date last_update_date

calendar table - Record count 100,000
---------------------------------------
week# day_of_week normalDate

REQUIREMENT : I need to update the target_drive table based on some rules

-----------------------------------------------------------------------------------------------------
FOR THE PROCESSING OF THE DATA I NEED THE FOLLOWING QUERY: BUT ITS TAKING 9 HRS TO RUN!! :(
-----------------------------------------------------------------------------------------------------

INSERT INTO WM_AD_HOC.E_SLS_INV_TEMP
(SKU,STORE,TRAN_DATE,REPORT_CODE,
SALES_UNITS,SALES_RETAIL,
OH_UNITS,OH_RETAIL)
SELECT SLS_DT.SKU,SLS_DT.STORE,SLS_DT.TRAN_DT,SLS_DT.REPORT_CODE,
SLS_DT.SALES_UNITS,SLS_DT.SALES_RETAIL,
INV_DT.OH_UNITS,INV_DT.OH_RETAIL
FROM

( SELECT SLS.SKU,SLS.STORE,CAL.NORMAL_DATE AS TRAN_DT,REPORT_CODE,
SUM(SAT_QTY) SALES_UNITS,SUM(SAT_SALES_AMT) SALES_RETAIL
FROM SALES_TBL
JOIN CALENDAR CAL
ON SLS.WEEK_NO = CAL.WEEK_NO
JOIN TARGET_DRIVE_TBL T1
ON SLS.SKU = T1.SKU
AND SLS.STORE = T1.STORE
WHERE T1.ITEM_INCL_FLAG = 'I'
AND CAL.NORMAL_DATE >= T1.LAST_UPDATE_DT
AND SLS.SAT_QTY<>0 AND CAL.DAY_OF_WEEK = 1
GROUP BY SLS.SKU,SLS.STORE,CAL.NORMAL_DATE,REPORT_CODE
UNION ALL
SELECT SLS.SKU,SLS.STORE,CAL.NORMAL_DATE AS TRAN_DT,REPORT_CODE,
SUM(SUN_QTY) SALES_UNITS,SUM(SUN_SALES_AMT) SALES_RETAIL
FROM SALES_TBL
JOIN CALENDAR CAL
ON SLS.WEEK_NO = CAL.WEEK_NO
JOIN TARGET_DRIVE_TBL T1
ON SLS.SKU = T1.SKU
AND SLS.STORE = T1.STORE
WHERE T1.ITEM_INCL_FLAG = 'I'
AND CAL.NORMAL_DATE >= T1.LAST_UPDATE_DT
AND SLS.SAT_QTY<>0 AND CAL.DAY_OF_WEEK = 2
GROUP BY SLS.SKU,SLS.STORE,CAL.NORMAL_DATE,REPORT_CODE
UNION ALL
SELECT SLS.SKU,SLS.STORE,CAL.NORMAL_DATE AS TRAN_DT,REPORT_CODE,
SUM(MON_QTY) SALES_UNITS,SUM(MON_SALES_AMT) SALES_RETAIL
FROM SALES_TBL
JOIN CALENDAR CAL
ON SLS.WEEK_NO = CAL.WEEK_NO
JOIN TARGET_DRIVE_TBL T1
ON SLS.SKU = T1.SKU
AND SLS.STORE = T1.STORE
WHERE T1.ITEM_INCL_FLAG = 'I'
AND CAL.NORMAL_DATE >= T1.LAST_UPDATE_DT
AND SLS.SAT_QTY<>0 AND CAL.DAY_OF_WEEK = 3
GROUP BY SLS.SKU,SLS.STORE,CAL.NORMAL_DATE,REPORT_CODE
UNION ALL
SELECT SLS.SKU,SLS.STORE,CAL.NORMAL_DATE AS TRAN_DT,REPORT_CODE,
SUM(TUE_QTY) SALES_UNITS,SUM(TUE_SALES_AMT) SALES_RETAIL
FROM SALES_TBL
JOIN CALENDAR CAL
ON SLS.WEEK_NO = CAL.WEEK_NO
JOIN TARGET_DRIVE_TBL T1
ON SLS.SKU = T1.SKU
AND SLS.STORE = T1.STORE
WHERE T1.ITEM_INCL_FLAG = 'I'
AND CAL.NORMAL_DATE >= T1.LAST_UPDATE_DT
AND SLS.SAT_QTY<>0 AND CAL.DAY_OF_WEEK = 4
GROUP BY SLS.SKU,SLS.STORE,CAL.NORMAL_DATE,REPORT_CODE
UNION ALL
SELECT SLS.SKU,SLS.STORE,CAL.NORMAL_DATE AS TRAN_DT,REPORT_CODE,
SUM(WED_QTY) SALES_UNITS,SUM(WED_SALES_AMT) SALES_RETAIL
FROM SALES_TBL
JOIN CALENDAR CAL
ON SLS.WEEK_NO = CAL.WEEK_NO
JOIN TARGET_DRIVE_TBL T1
ON SLS.SKU = T1.SKU
AND SLS.STORE = T1.STORE
WHERE T1.ITEM_INCL_FLAG = 'I'
AND CAL.NORMAL_DATE >= T1.LAST_UPDATE_DT
AND SLS.SAT_QTY<>0 AND CAL.DAY_OF_WEEK = 5
GROUP BY SLS.SKU,SLS.STORE,CAL.NORMAL_DATE,REPORT_CODE
UNION ALL
SELECT SLS.SKU,SLS.STORE,CAL.NORMAL_DATE AS TRAN_DT,REPORT_CODE,
SUM(THU_QTY) SALES_UNITS,SUM(THU_SALES_AMT) SALES_RETAIL
FROM SALES_TBL
JOIN CALENDAR CAL
ON SLS.WEEK_NO = CAL.WEEK_NO
JOIN TARGET_DRIVE_TBL T1
ON SLS.SKU = T1.SKU
AND SLS.STORE = T1.STORE
WHERE T1.ITEM_INCL_FLAG = 'I'
AND CAL.NORMAL_DATE >= T1.LAST_UPDATE_DT
AND SLS.SAT_QTY<>0 AND CAL.DAY_OF_WEEK = 6
GROUP BY SLS.SKU,SLS.STORE,CAL.NORMAL_DATE,REPORT_CODE
UNION ALL
SELECT SLS.SKU,SLS.STORE,CAL.NORMAL_DATE AS TRAN_DT,REPORT_CODE,
SUM(FRI_QTY) SALES_UNITS,SUM(FRI_SALES_AMT) SALES_RETAIL
FROM SALES_TBL
JOIN CALENDAR CAL
ON SLS.WEEK_NO = CAL.WEEK_NO
JOIN TARGET_DRIVE_TBL T1
ON SLS.SKU = T1.SKU
AND SLS.STORE = T1.STORE
WHERE T1.ITEM_INCL_FLAG = 'I'
AND CAL.NORMAL_DATE >= T1.LAST_UPDATE_DT
AND SLS.SAT_QTY<>0 AND CAL.DAY_OF_WEEK = 7
GROUP BY SLS.SKU,SLS.STORE,CAL.NORMAL_DATE,REPORT_CODE
) SLS_DT,
(
SELECT INV.SKU,INV.STORE,CAL1.NORMAL_DATE AS TRAN_DT,
SUM(INV.SAT_ON_HAND_QTY) OH_UNITS, SUM(INV.SAT_ON_HAND_QTY*INV.AMOUNT) OH_RETAIL
FROM INVENTORY_TBL INV
JOIN TARGET_DRIVE_TBL T2
ON T2.STORE = INV.STORE
AND T2.SKU = INV.STORE
JOIN CALENDAR CAL1
ON CAL1.WEEK_NO = INV.WEEK_NO
WHERE T2.ITEM_INCL_FLAG = 'I'
AND CAL1.NORMAL_DATE >= T2.LAST_UPDATE_DT
AND INV.SAT_ON_HAND_QTY <> 0 AND CAL1.DAY_OF_WEEK = 1
GROUP BY INV.SKU,INV.STORE,CAL1.NORMAL_DATE
UNION ALL
SELECT INV.SKU,INV.STORE,CAL1.NORMAL_DATE AS TRAN_DT,
SUM(INV.SUN_ON_HAND_QTY) OH_UNITS, SUM(INV.SUN_ON_HAND_QTY*INV.AMOUNT) OH_RETAIL
FROM INVENTORY_TBL INV
JOIN TARGET_DRIVE_TBL T2
ON T2.STORE = INV.STORE
AND T2.SKU = INV.STORE
JOIN CALENDAR CAL1
ON CAL1.WEEK_NO = INV.WEEK_NO
WHERE T2.ITEM_INCL_FLAG = 'I'
AND CAL1.NORMAL_DATE >= T2.LAST_UPDATE_DT
AND INV.SAT_ON_HAND_QTY <> 0 AND CAL1.DAY_OF_WEEK = 2
GROUP BY INV.SKU,INV.STORE,CAL1.NORMAL_DATE
UNION ALL
SELECT INV.SKU,INV.STORE,CAL1.NORMAL_DATE AS TRAN_DT,
SUM(INV.MON_ON_HAND_QTY) OH_UNITS, SUM(INV.MON_ON_HAND_QTY*INV.AMOUNT) OH_RETAIL
FROM INVENTORY_TBL INV
JOIN TARGET_DRIVE_TBL T2
ON T2.STORE = INV.STORE
AND T2.SKU = INV.STORE
JOIN CALENDAR CAL1
ON CAL1.WEEK_NO = INV.WEEK_NO
WHERE T2.ITEM_INCL_FLAG = 'I'
AND CAL1.NORMAL_DATE >= T2.LAST_UPDATE_DT
AND INV.SAT_ON_HAND_QTY <> 0 AND CAL1.DAY_OF_WEEK = 3
GROUP BY INV.SKU,INV.STORE,CAL1.NORMAL_DATE
UNION ALL
SELECT INV.SKU,INV.STORE,CAL1.NORMAL_DATE AS TRAN_DT,
SUM(INV.TUE_ON_HAND_QTY) OH_UNITS, SUM(INV.TUE_ON_HAND_QTY*INV.AMOUNT) OH_RETAIL
FROM INVENTORY_TBL INV
JOIN TARGET_DRIVE_TBL T2
ON T2.STORE = INV.STORE
AND T2.SKU = INV.STORE
JOIN CALENDAR CAL1
ON CAL1.WEEK_NO = INV.WEEK_NO
WHERE T2.ITEM_INCL_FLAG = 'I'
AND CAL1.NORMAL_DATE >= T2.LAST_UPDATE_DT
AND INV.SAT_ON_HAND_QTY <> 0 AND CAL1.DAY_OF_WEEK = 4
GROUP BY INV.SKU,INV.STORE,CAL1.NORMAL_DATE
UNION ALL
SELECT INV.SKU,INV.STORE,CAL1.NORMAL_DATE AS TRAN_DT,
SUM(INV.WED_ON_HAND_QTY) OH_UNITS, SUM(INV.WED_ON_HAND_QTY*INV.AMOUNT) OH_RETAIL
FROM INVENTORY_TBL INV
JOIN TARGET_DRIVE_TBL T2
ON T2.STORE = INV.STORE
AND T2.SKU = INV.STORE
JOIN CALENDAR CAL1
ON CAL1.WEEK_NO = INV.WEEK_NO
WHERE T2.ITEM_INCL_FLAG = 'I'
AND CAL1.NORMAL_DATE >= T2.LAST_UPDATE_DT
AND INV.SAT_ON_HAND_QTY <> 0 AND CAL1.DAY_OF_WEEK = 5
GROUP BY INV.SKU,INV.STORE,CAL1.NORMAL_DATE
UNION ALL
SELECT INV.SKU,INV.STORE,CAL1.NORMAL_DATE AS TRAN_DT,
SUM(INV.THU_ON_HAND_QTY) OH_UNITS, SUM(INV.THU_ON_HAND_QTY*INV.AMOUNT) OH_RETAIL
FROM INVENTORY_TBL INV
JOIN TARGET_DRIVE_TBL T2
ON T2.STORE = INV.STORE
AND T2.SKU = INV.STORE
JOIN CALENDAR CAL1
ON CAL1.WEEK_NO = INV.WEEK_NO
WHERE T2.ITEM_INCL_FLAG = 'I'
AND CAL1.NORMAL_DATE >= T2.LAST_UPDATE_DT
AND INV.SAT_ON_HAND_QTY <> 0 AND CAL1.DAY_OF_WEEK = 6
GROUP BY INV.SKU,INV.STORE,CAL1.NORMAL_DATE
UNION ALL
SELECT INV.SKU,INV.STORE,CAL1.NORMAL_DATE AS TRAN_DT,
SUM(INV.FRI_ON_HAND_QTY) OH_UNITS, SUM(INV.FRI_ON_HAND_QTY*INV.AMOUNT) OH_RETAIL
FROM INVENTORY_TBL INV
JOIN TARGET_DRIVE_TBL T2
ON T2.STORE = INV.STORE
AND T2.SKU = INV.STORE
JOIN CALENDAR CAL1
ON CAL1.WEEK_NO = INV.WEEK_NO
WHERE T2.ITEM_INCL_FLAG = 'I'
AND CAL1.NORMAL_DATE >= T2.LAST_UPDATE_DT
AND INV.SAT_ON_HAND_QTY <> 0 AND CAL1.DAY_OF_WEEK = 7
GROUP BY INV.SKU,INV.STORE,CAL1.NORMAL_DATE
) INV_DT
WHERE SLS_DT.SALES_UNITS > 0