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} */
