[Home] [Help]
258: SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_TRANSFERRED',1)) EMAILS_TRANSFERRED,
259: SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_DELETED',1)) EMAILS_DELETED,
260: SUM(DECODE(ih_lc_seg_typs.MILCS_CODE,'EMAIL_REPLY',
261: (ih_lc_segs.start_date_time - ih_mitem.start_date_time) * 24 * 60 * 60)) EMC_RESPONSE_TIME
262: FROM JTF_IH_MEDIA_ITEMS ih_mitem,
263: JTF_IH_MEDIA_ITEM_LC_SEGS ih_lc_segs,
264: (
265: --
266: --To calculate agent response time
557: NVL(ih_mitem.source_id,-1) EMAIL_ACCOUNT_ID,
558: NVL(iem_r_c.ROUTE_CLASSIFICATION_ID,-1) CLASSIFICATION_ID,
559: ih_lc_segs_fetch.START_DATE_TIME start_date_time,
560: g_rounded_collect_end_date end_date_time
561: FROM JTF_IH_MEDIA_ITEMS ih_mitem,
562: JTF_IH_MEDIA_ITEM_LC_SEGS ih_lc_segs_fetch,
563: JTF_IH_MEDIA_ITM_LC_SEG_TYS ih_lc_seg_typs,
564: --IEM_ROUTE_CLASSIFICATIONS iem_r_c
565: --
596: NVL(a.source_id,-1) EMAIL_ACCOUNT_ID,
597: NVL(iem_r_c.ROUTE_CLASSIFICATION_ID,-1) CLASSIFICATION_ID,
598: Y.START_DATE_TIME start_date_time,
599: MIN(X.START_DATE_TIME) end_date_time
600: FROM JTF_IH_MEDIA_ITEMS A,
601: (
602: SELECT MEDIA_ID,
603: RESOURCE_ID,
604: START_DATE_TIME
822: NVL(ih_lc_segs_fetch.RESOURCE_ID,-999) RESOURCE_ID,
823: ih_lc_segs_fetch.START_DATE_TIME start_date_time, --open start time for this specific agent
824: g_rounded_collect_end_date end_date_time, --since it is still open at g_collect_end_time
825: min(A.start_date_time) email_start_date_time --very first time that the email was open in ANY of the agents
826: FROM JTF_IH_MEDIA_ITEMS ih_mitem,
827: JTF_IH_MEDIA_ITEM_LC_SEGS ih_lc_segs_fetch,
828: jtf_ih_media_item_lc_segs A,
829: JTF_IH_MEDIA_ITM_LC_SEG_TYS ih_lc_seg_typs,
830: jtf_ih_media_itm_lc_seg_tys B,
880: NVL(Y.RESOURCE_ID,-999) RESOURCE_ID,
881: Y.START_DATE_TIME start_date_time,
882: MIN(X.START_DATE_TIME) end_date_time, --to calculate open times
883: min(Y1.start_date_time) email_start_date_time
884: FROM JTF_IH_MEDIA_ITEMS A,
885: (
886: SELECT MEDIA_ID,
887: RESOURCE_ID,
888: START_DATE_TIME