Search code examples
sqloracle-databasetelephony

Oracle SQL Statement - Identify & Count Unique Callers

I'm looking to make some improvements to our telephony call data - and have a requirement to identify if a CALLER is unique - if they call more than once on a given date (CALL_DATE) - it flags as a 1 value, if only once a 0 value.

Any ideas how I can modify this existing statement to reflect this?

SELECT /*+ PARALLEL (4) */
A.CALL_ID,
A.CALL_DATE,
O.OT_OUTLET_CODE,
A.CALL_TIME,
TO_CHAR(TO_DATE(A.CALL_TIME, 'HH24:MI:SS')+A.TALK_TIME/(24*60*60),'HH24:MI:SS') "CALL_END_TIME",
A.TALK_TIME,
A.RING_TIME,
A.OUTCOME,
CASE WHEN A.TRANSFER_TO = '10000' THEN 1 ELSE 0 END AS "VOICEMAIL"
FROM
    OWBI.ODS_FACT_TIGER_TELEPHONY A,
    OWBI.WHS_DIM_CAL_DATE C,
    OWBI.WHS_DIM_OUTLET O
WHERE
                A.CALL_DATE = C.CD_DAY_DATE
        AND     A.WHS_DIM_OUTLET = O.DIMENSION_KEY
        AND     C.EY_YEAR_CODE IN       ('2019')
        AND     C.EW_WEEK_IN_YEAR IN    ('1')      --  **FILTER ON PREVIOUS BUSINESS WEEK NUMBER**

ORDER BY A.CALL_DATE DESC;
Solution

  • What you are describing sounds like a job for the analytic count(*) function.

    Add this to the SELECT clause and don't change anything else:

    case when count(*) over (partition by a.call_id, a.call_date) = 1 then 0
     else 1 end as unique_flag