CREATE VIEW vwinteractionlegdata AS (
 WITH seg AS (
         SELECT di.conversationid,
            di.conversationstartdateltc,
            di.conversationenddateltc,
            di.originaldirection,
            di.mediatype,
            di.purpose,
            di.segmenttype,
            di.queueid,
            di.queuename,
            di.userid,
            di.agentname,
            di.segmentstartdateltc,
            di.segmenttime,
            (split_part((di.gencode)::text, '|'::text, 1))::integer AS gen1
           FROM vwdetailedinteractiondata di
        ), queue_wait AS (
         SELECT seg.conversationid,
            seg.queueid,
            sum(seg.segmenttime) AS queue_time
           FROM seg
          WHERE (((seg.purpose)::text = 'acd'::text) AND ((seg.segmenttype)::text = 'interact'::text))
          GROUP BY seg.conversationid, seg.queueid
        ), ivr_time AS (
         SELECT seg.conversationid,
            sum(seg.segmenttime) AS ivr_time
           FROM seg
          WHERE (((seg.purpose)::text = 'ivr'::text) AND ((seg.segmenttype)::text = 'ivr'::text))
          GROUP BY seg.conversationid
        ), agent_legs AS (
         SELECT seg.conversationid,
            seg.gen1,
            max((seg.queueid)::text) AS queueid,
            max((seg.queuename)::text) AS queuename,
            max((seg.userid)::text) AS userid,
            max((seg.agentname)::text) AS agentname,
            max(seg.conversationstartdateltc) AS conversationstartdateltc,
            max(seg.conversationenddateltc) AS conversationenddateltc,
            max((seg.originaldirection)::text) AS direction,
            max((seg.mediatype)::text) AS mediatype,
            min(
                CASE
                    WHEN ((seg.segmenttype)::text = 'interact'::text) THEN seg.segmentstartdateltc
                    ELSE NULL::timestamp without time zone
                END) AS interactionstartdateltc,
            sum(
                CASE
                    WHEN ((seg.segmenttype)::text = 'interact'::text) THEN seg.segmenttime
                    ELSE (0)::numeric
                END) AS talk_time,
            sum(
                CASE
                    WHEN ((seg.segmenttype)::text = 'hold'::text) THEN seg.segmenttime
                    ELSE (0)::numeric
                END) AS hold_time,
            sum(
                CASE
                    WHEN ((seg.segmenttype)::text = 'wrapup'::text) THEN seg.segmenttime
                    ELSE (0)::numeric
                END) AS wrap_up_time
           FROM seg
          WHERE ((seg.purpose)::text = 'agent'::text)
          GROUP BY seg.conversationid, seg.gen1
         HAVING (sum(
                CASE
                    WHEN ((seg.segmenttype)::text = 'interact'::text) THEN 1
                    ELSE 0
                END) > 0)
        ), legs_numbered AS (
         SELECT al.conversationid,
            al.gen1,
            al.queueid,
            al.queuename,
            al.userid,
            al.agentname,
            al.conversationstartdateltc,
            al.conversationenddateltc,
            al.direction,
            al.mediatype,
            al.interactionstartdateltc,
            al.talk_time,
            al.hold_time,
            al.wrap_up_time,
            row_number() OVER (PARTITION BY al.conversationid ORDER BY al.interactionstartdateltc, al.gen1) AS conversationleg,
            lead(al.queuename) OVER (PARTITION BY al.conversationid ORDER BY al.interactionstartdateltc, al.gen1) AS transferred_to_queue,
            lag(al.queuename) OVER (PARTITION BY al.conversationid ORDER BY al.interactionstartdateltc, al.gen1) AS transferred_from_queue
           FROM agent_legs al
        )
 SELECT l.conversationid,
    (((l.conversationid)::text || '_'::text) || l.conversationleg) AS interactionid,
    l.conversationstartdateltc,
    l.conversationenddateltc,
    l.direction,
    l.mediatype,
    l.conversationleg,
    l.interactionstartdateltc,
    l.queuename,
    l.agentname,
        CASE
            WHEN (l.conversationleg = 1) THEN COALESCE(iv.ivr_time, (0)::numeric)
            ELSE (0)::numeric
        END AS ivr_time,
    COALESCE(qw.queue_time, (0)::numeric) AS queue_time,
    l.talk_time,
    l.hold_time,
    l.wrap_up_time,
        CASE
            WHEN (l.transferred_to_queue IS NOT NULL) THEN 1
            ELSE 0
        END AS transferred_flag,
    l.transferred_to_queue,
    l.transferred_from_queue
   FROM ((legs_numbered l
     LEFT JOIN queue_wait qw ON ((((l.conversationid)::text = (qw.conversationid)::text) AND (l.queueid = (qw.queueid)::text))))
     LEFT JOIN ivr_time iv ON (((l.conversationid)::text = (iv.conversationid)::text)))
  ORDER BY l.conversationstartdateltc DESC, l.conversationid, l.conversationleg
)