SELECT /*+ INDEX(FILTERED_V_INTERACTION_FACT.FILTERED_V_INT_FACT_INNER.mif.msf PK_M_SEGMENT_FACT) INDEX(FILTERED_V_INTERACTION_FACT.FILTERED_V_INT_FACT_INNER.mif.irfug PK_IRF_USER_DATA_GEN_1) INDEX(FILTERED_V_INTERACTION_FACT.FILTERED_V_INT_FACT_INNER.mif.irfug2 PK_IRF_USER_DATA_GEN_1) */ 1, FILTERED_V_INTERACTION_FACT.TENANT_KEY, TENANT.TENANT_NAME, FILTERED_V_INTERACTION_FACT.MEDIA_TYPE_KEY, MEDIA_TYPE.MEDIA_NAME, FILTERED_V_INTERACTION_FACT.INTERACTION_TYPE_KEY, INTERACTION_TYPE_GI2.INTERACTION_TYPE, FILTERED_V_INTERACTION_FACT.INTERACTION_ID, FILTERED_V_INTERACTION_FACT.START_TS, FILTERED_V_INTERACTION_FACT.START_TIME, FILTERED_V_INTERACTION_FACT.END_TIME, cast(FILTERED_V_INTERACTION_FACT.MEDIA_SERVER_IXN_ID as char(20)), FILTERED_V_INTERACTION_FACT.MEDIA_SERVER_IXN_GUID, FILTERED_V_INTERACTION_FACT.MEDIA_SERVER_ROOT_IXN_ID, FILTERED_V_INTERACTION_FACT.SOURCE_ADDRESS, FILTERED_V_INTERACTION_FACT.TARGET_ADDRESS, RESOURCE_Q.RESOURCE_NAME, RESOURCE_Q.RESOURCE_TYPE, RESOURCE_.RESOURCE_NAME, RESOURCE_.RESOURCE_TYPE, FILTERED_V_INTERACTION_FACT.TECHNICAL_DESCRIPTOR_KEY, TECHNICAL_DESCRIPTOR.RESOURCE_ROLE, TECHNICAL_DESCRIPTOR.ROLE_REASON, TECHNICAL_DESCRIPTOR.RESULT_REASON, TECHNICAL_DESCRIPTOR.TECHNICAL_RESULT, FILTERED_V_INTERACTION_FACT.TOTAL_DURATION, FILTERED_V_INTERACTION_FACT.ACTIVE_FLAG, FILTERED_V_INTERACTION_FACT.ORDER_KEY, FILTERED_V_INTERACTION_FACT.SEGMENT_ID, FILTERED_V_INTERACTION_FACT.CUSTOMER_ID FROM TENANT INNER JOIN ( select * from ( select INTERACTION_ID, INTERACTION_RESOURCE_ID, INTERACTION_TYPE_KEY, START_TS, START_TIME, END_TIME, MEDIA_TYPE_KEY, RESOURCE_KEY, SRC_RESOURCE_KEY, TOTAL_DURATION, TECHNICAL_DESCRIPTOR_KEY, TENANT_KEY, ORDER_KEY, CUSTOMER_ID, SEGMENT_ID, SOURCE_ADDRESS, TARGET_ADDRESS, ACTIVE_FLAG, MEDIA_SERVER_IXN_ID, MEDIA_SERVER_IXN_GUID, MEDIA_SERVER_ROOT_IXN_ID from ( select irf.INTERACTION_ID, irf.INTERACTION_RESOURCE_ID, irf.INTERACTION_TYPE_KEY, itf.START_TS_TIME START_TS, irf.START_TS_TIME START_TIME, irf.END_TS_TIME END_TIME, irf.MEDIA_TYPE_KEY, irf.RESOURCE_KEY, irf.MEDIATION_RESOURCE_KEY SRC_RESOURCE_KEY, (irf.END_TS - irf.START_TS) as TOTAL_DURATION, irf.TECHNICAL_DESCRIPTOR_KEY, irf.TENANT_KEY, irfug.CUSTOMER_ID, case when td.RESOURCE_ROLE_CODE in ('INITIATEDCONSULT','INITIATED') then 1 else 3 end AS ORDER_KEY, irf.INTERACTION_RESOURCE_ID SEGMENT_ID, itf.SOURCE_ADDRESS SOURCE_ADDRESS, itf.TARGET_ADDRESS TARGET_ADDRESS, irf.ACTIVE_FLAG, itf.MEDIA_SERVER_IXN_ID, itf.MEDIA_SERVER_IXN_GUID, itf.MEDIA_SERVER_ROOT_IXN_ID from INTERACTION_RESOURCE_FACT_GI2 irf inner join RESOURCE_GI2 res ON (res.RESOURCE_KEY = irf.RESOURCE_KEY and res.RESOURCE_TYPE_CODE not in ('QUEUE')) inner join TECHNICAL_DESCRIPTOR td ON (td.TECHNICAL_DESCRIPTOR_KEY = irf.TECHNICAL_DESCRIPTOR_KEY) inner join INTERACTION_FACT_GI2 itf ON (itf.INTERACTION_ID = irf.INTERACTION_ID and itf.START_DATE_TIME_KEY = irf.INTERACTION_SDT_KEY and itf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121)))- 2764800 and itf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121)))+2764800 ) inner join IRF_USER_DATA_GEN_1 irfug ON (irfug.INTERACTION_RESOURCE_ID = irf.INTERACTION_RESOURCE_ID and irfug.START_DATE_TIME_KEY = irf.START_DATE_TIME_KEY) where 1 = 1 and itf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) and irf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121))) and irf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) and irfug.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121))) and irfug.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) union all select msf.INTERACTION_ID, cast(-2 as integer) INTERACTION_RESOURCE_ID, msf.INTERACTION_TYPE_KEY, itf.START_TS_TIME START_TS, msf.START_TS_TIME START_TIME, msf.END_TS_TIME END_TIME, msf.MEDIA_TYPE_KEY, msf.RESOURCE_KEY RESOURCE_KEY, cast(-2 as integer) SRC_RESOURCE_KEY, msf.MEDIATION_DURATION TOTAL_DURATION, msf.TECHNICAL_DESCRIPTOR_KEY, msf.TENANT_KEY, irfug2.CUSTOMER_ID, 2 as ORDER_KEY, msf.MEDIATION_SEGMENT_ID SEGMENT_ID, itf.SOURCE_ADDRESS SOURCE_ADDRESS, itf.TARGET_ADDRESS TARGET_ADDRESS, msf.ACTIVE_FLAG, itf.MEDIA_SERVER_IXN_ID, itf.MEDIA_SERVER_IXN_GUID, itf.MEDIA_SERVER_ROOT_IXN_ID from MEDIATION_SEGMENT_FACT_GI2 msf inner join INTERACTION_FACT_GI2 itf ON (itf.INTERACTION_ID = msf.INTERACTION_ID and itf.START_DATE_TIME_KEY = msf.INTERACTION_SDT_KEY and itf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121)))- 2764800 and itf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121)))+2764800 ) left outer join INTERACTION_RESOURCE_FACT irf ON (irf.INTERACTION_RESOURCE_ID = msf.TARGET_IXN_RESOURCE_ID and irf.START_DATE_TIME_KEY = msf.TARGET_IXN_RESOURCE_SDT_KEY and irf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121)))- 2764800 and irf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121)))+2764800 ) left outer join IRF_USER_DATA_GEN_1 irfug2 ON (irfug2.INTERACTION_RESOURCE_ID = irf.INTERACTION_RESOURCE_ID and irfug2.START_DATE_TIME_KEY = irf.START_DATE_TIME_KEY and irfug2.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121)))- 2764800 and irfug2.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121)))+2764800 ) where 1 = 1 and itf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) and msf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121))) and msf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) and itf.START_DATE_TIME_KEY <= (SELECT MIN(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT MAX(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) ) mif where mif.INTERACTION_ID in ( select irf.INTERACTION_ID from INTERACTION_RESOURCE_FACT_GI2 irf inner join INTERACTION_TYPE ON (INTERACTION_TYPE.INTERACTION_TYPE_KEY = irf.INTERACTION_TYPE_KEY) inner join TENANT ON (TENANT.TENANT_KEY = irf.TENANT_KEY) inner join MEDIA_TYPE ON (MEDIA_TYPE.MEDIA_TYPE_KEY = irf.MEDIA_TYPE_KEY) inner join RESOURCE_GI2 ON (RESOURCE_GI2.RESOURCE_KEY = irf.RESOURCE_KEY) where 1 = 1 and irf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121))) and irf.START_DATE_TIME_KEY <= (SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) and irf.START_TS_TIME >=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121) and irf.START_TS_TIME <=CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121) and irf.START_DATE_TIME_KEY > 0 union all select msf.INTERACTION_ID from MEDIATION_SEGMENT_FACT_GI2 msf inner join INTERACTION_TYPE ON (INTERACTION_TYPE.INTERACTION_TYPE_KEY = msf.INTERACTION_TYPE_KEY) inner join TENANT ON (TENANT.TENANT_KEY = msf.TENANT_KEY) inner join MEDIA_TYPE ON (MEDIA_TYPE.MEDIA_TYPE_KEY = msf.MEDIA_TYPE_KEY) inner join RESOURCE_ RESOURCE_Q ON (RESOURCE_Q.RESOURCE_KEY = msf.RESOURCE_KEY) where 1 = 1 and msf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121))) and msf.START_DATE_TIME_KEY <=(SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) and msf.START_TS_TIME >= CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121) and msf.START_TS_TIME <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121) and msf.START_DATE_TIME_KEY > 0 union all select irf.INTERACTION_ID FROM INTERACTION_RESOURCE_FACT_GI2 irf INNER JOIN MEDIATION_SEGMENT_FACT msf ON (irf.INTERACTION_ID = msf.INTERACTION_ID) INNER JOIN INTERACTION_TYPE ON (INTERACTION_TYPE.INTERACTION_TYPE_KEY = irf.INTERACTION_TYPE_KEY and INTERACTION_TYPE.INTERACTION_TYPE_KEY = msf.INTERACTION_TYPE_KEY) INNER JOIN TENANT ON (TENANT.TENANT_KEY = irf.TENANT_KEY and TENANT.TENANT_KEY = msf.TENANT_KEY) INNER JOIN MEDIA_TYPE ON (MEDIA_TYPE.MEDIA_TYPE_KEY = irf.MEDIA_TYPE_KEY and MEDIA_TYPE.MEDIA_TYPE_KEY = msf.MEDIA_TYPE_KEY) INNER JOIN RESOURCE_GI2 ON (RESOURCE_GI2.RESOURCE_KEY = irf.RESOURCE_KEY) INNER JOIN RESOURCE_ RESOURCE_Q ON (RESOURCE_Q.RESOURCE_KEY = msf.RESOURCE_KEY) where 1= 1 and irf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121))) and irf.START_DATE_TIME_KEY <=(SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) and irf.START_TS_TIME >=CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121) and irf.START_TS_TIME <=CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121) and msf.START_DATE_TIME_KEY >= (SELECT min(DATE_TIME_KEY) from DATE_TIME WHERE CAL_DATE IN (select max(CAL_DATE) FROM DATE_TIME WHERE CAL_DATE <= CONVERT(datetime2, '2020-11-03 00:00:00.0000000', 121))) and msf.START_DATE_TIME_KEY <=(SELECT Min(DATE_TIME_KEY) FROM DATE_TIME WHERE CAL_DATE IN (SELECT Max(CAL_DATE) FROM DATE_TIME where CAL_DATE <= CONVERT(datetime2, '2020-11-04 00:00:00.0000000', 121))) ) ) FILTERED_V_INT_FACT_INNER ) FILTERED_V_INTERACTION_FACT ON (FILTERED_V_INTERACTION_FACT.TENANT_KEY=TENANT.TENANT_KEY) INNER JOIN INTERACTION_TYPE_GI2 ON (FILTERED_V_INTERACTION_FACT.INTERACTION_TYPE_KEY=INTERACTION_TYPE_GI2.INTERACTION_TYPE_KEY) INNER JOIN MEDIA_TYPE ON (MEDIA_TYPE.MEDIA_TYPE_KEY=FILTERED_V_INTERACTION_FACT.MEDIA_TYPE_KEY) INNER JOIN RESOURCE_ ON (FILTERED_V_INTERACTION_FACT.RESOURCE_KEY=RESOURCE_.RESOURCE_KEY) INNER JOIN TECHNICAL_DESCRIPTOR ON (TECHNICAL_DESCRIPTOR.TECHNICAL_DESCRIPTOR_KEY=FILTERED_V_INTERACTION_FACT.TECHNICAL_DESCRIPTOR_KEY) LEFT OUTER JOIN RESOURCE_ RESOURCE_Q ON (RESOURCE_Q.RESOURCE_KEY=FILTERED_V_INTERACTION_FACT.SRC_RESOURCE_KEY) WHERE 1=1 /* ${IterationLength=3117600} */