store_traffic.sql

Source: DMF/PuC/AIF/DR1 scripts/store_traffic.sql

-- STORE TRAFFIC --
 
-- 1. Insert from RAW (ORI_STORE_TRAFFIC_RAW) into CTRL (ORI_STORE_TRAFFIC_CTRL) table:
 
INSERT INTO ORI_STORE_TRAFFIC_CTRL(ORG_NUM
                                  ,DAY_DT
                                  ,HOUR_24_NUM
                                  ,MINUTE_NUM
                                  ,STORE_TRAFFIC
								  ,CHANGED_BY_ID
                                  ,CHANGED_ON_DT
                                  ,DATASOURCE_NUM_ID
                                  ,INTEGRATION_ID)
     SELECT LTRIM(r.ORG_NUM, '0')               ORG_NUM
	             ,r.DAY_DT                      DAY_DT
                 ,LPAD(r.HOUR_24_NUM, 2, '0')   HOUR_24_NUM       
                 ,LPAD(r.MINUTE_NUM, 2, '0')    MINUTE_NUM
                 ,r.STORE_TRAFFIC               STORE_TRAFFIC
	             ,'DMF'                         CHANGED_BY_ID
	             ,SYSDATE                       CHANGED_ON_DT
                 ,'1'                           DATASOURCE_NUM_ID
                 ,LTRIM(r.ORG_NUM, '0') || '~' || TO_CHAR(r.DAY_DT, 'YYYYMMDD') || '~' || LPAD(HOUR_24_NUM, 2, '0') || '~' || LPAD(MINUTE_NUM, 2, '0') || '~' || '1'    INTEGRATION_ID
 FROM ORI_STORE_TRAFFIC_RAW r
 JOIN STORE_ADD_CTRL st
   ON st.CTRL_STATUS = 'C'
  AND st.STORE = r.ORG_NUM; 			
 
 
-- 3. Export STORE_TRAFFIC.csv
 
python oracleExport_store_traffic.py
 
mv STORE_TRAFFIC.csv W_RTL_STTRFC_LC_DY_MI_FS.dat