Help with the query

Analytics

Help with the query

Here is the query. i wanted to add logic - discount the same caller multiple times within a rolling three day period.

The way it works now is by ranking and partitioning data to determine gaps between calling on each interaction. My thought is to keep the core SQL as is, but figure out how to aggregate customers within the rolling three day window to alleviate the issue of multiple callbacks from the same customer impacting FCR %. Do you have any ideas?

SELECT 

                a.GroupName, a.agent_name,a.ani,a.startdatetime, b.ani AS repeat_ani, b.startdatetime AS repeat_date, d.month_num, d.week_end_date, d.day_date,1 as Volume,

CASE WHEN busHours <=72 THEN 1

else 0 end AS repeat_bucket, case when b.startdatetime IS NULL then NULL else count(e.cal_day_dt)-1 end as busDays, extract(hour from a.startdatetime) as startHour, extract(hour from b.startdatetime) as EndHour,

(busDays * 24) + (endHour - startHour) as busHours

FROM

(SELECT CASE WHEN EnterpriseName IN ('WEB_DIFMCS_Q_CT', 'WEB_DIFM.Cancel_Q_CT', 'WEB_Retention_Q_CT','WEB_SM.CS_Q_CT' ) THEN 'DIFM CS'

                WHEN EnterpriseName IN ('WEB_DIYCS_Q_CT', 'WEB_DIY.Cancel_Q_CT') THEN 'DIY CS'

                WHEN EnterpriseName IN ('WEB_FBBoost_Q_CT') THEN 'Facebook Boost'

                WHEN EnterpriseName IN ('WEB_LeadsGen_Q_CT') THEN 'Leads'

                WHEN EnterpriseName IN ('WEB_MODS.1st30_Q_CT', 'WEB_MODS_Q_CT', 'WEB_SM.MODS_Q_CT') THEN 'MODS'

                WHEN EnterpriseName IN ('WEB_PPC_Q_CT') then 'PPC Ops'

                WHEN EnterpriseName IN ('SigSeries_Q_CT', 'WEB_TaskForce_Q_CT') then 'Task Force'

                WHEN EnterpriseName IN ('WEB_TechSupport_Q_CT', 'WEB_SM.TS_Q_CT') THEN 'Jax TS'

                WHEN EnterpriseName IN ('WEB_Triage_Q_CT') THEN 'Triage'

                WHEN EnterpriseName IN ('WEB_SmartCalls_Q_CT') THEN 'SmartCalls Ops'

                WHEN EnterpriseName IN ('WEB_eCommSS_Q_CT') THEN 'Store Support'

                WHEN EnterpriseName IN ('WEB_UK.CS_Q_CT') THEN 'UK DIFM CS'

                WHEN EnterpriseName IN ('WEB_UK.TS_Q_CT') THEN 'UK TS'

    WHEN EnterpriseName IN ('WEB_TechSupport.RTS_Q_CT') THEN 'RTS'

                WHEN EnterpriseName IN ('WEB_UK.MODS_Q_CT') THEN 'UK MODS'

END AS GroupName

,agent_name, ani, startdatetime,rank()over(partition by GroupName||ani order by startdatetime) AS ranking

FROM

(SELECT TCD.datetime as startdatetime,

       TCD.CallTypeID, Call_Type.EnterpriseName,

                  TCD.ANI,

                   Person.FirstName||' '||Person.LastName as agent_name,

                  TCD.agentSkillTargetID

  FROM

                CiscoViewDB.Termination_Call_Detail as TCD

                INNER JOIN Call_Type ON Call_Type.CallTypeID = TCD.CallTypeID

                INNER JOIN Agent  ON (TCD.AgentSkillTargetID = Agent.SkillTargetID)

                INNER JOIN Person ON (Agent.PersonID = Person.PersonID)

                WHERE Person.FirstName IS NOT NULL and ani IS NOT NULL and CHARACTER_LENGTH(CAST(ANI as VARCHAR(10))) > 9 AND ani not in ('9046806600','6122075402','6122967202','9027492700','8008999724','9041234567','5707088400', 'Unknown', '6122759714','8003381771','6153293150','5863228556','5409989218','9053530732','9042085427','9027492488','8772724563','7708746182','8666648100','8646079179','5703594480','8887001012') AND CAST(startdatetime AS DATE FORMAT 'mm/dd/yyyy') >=  CURRENT_date-200) UC1

                ) a

LEFT OUTER JOIN

                (SELECT CASE WHEN EnterpriseName IN ('WEB_DIFMCS_Q_CT', 'WEB_DIFM.Cancel_Q_CT', 'WEB_Retention_Q_CT','WEB_SM.CS_Q_CT' ) THEN 'DIFM CS'

                WHEN EnterpriseName IN ('WEB_DIYCS_Q_CT', 'WEB_DIY.Cancel_Q_CT') THEN 'DIY CS'

                WHEN EnterpriseName IN ('WEB_FBBoost_Q_CT') THEN 'Facebook Boost'

                WHEN EnterpriseName IN ('WEB_LeadsGen_Q_CT') THEN 'Leads'

                WHEN EnterpriseName IN ('WEB_MODS.1st30_Q_CT', 'WEB_MODS_Q_CT', 'WEB_SM.MODS_Q_CT') THEN 'MODS'

                WHEN EnterpriseName IN ('WEB_PPC_Q_CT') then 'PPC Ops'

                WHEN EnterpriseName IN ('SigSeries_Q_CT', 'WEB_TaskForce_Q_CT') then 'Task Force'

                WHEN EnterpriseName IN ('WEB_TechSupport_Q_CT', 'WEB_SM.TS_Q_CT') THEN 'Jax TS'

                WHEN EnterpriseName IN ('WEB_Triage_Q_CT') THEN 'Triage'

                WHEN EnterpriseName IN ('WEB_SmartCalls_Q_CT') THEN 'SmartCalls Ops'

                WHEN EnterpriseName IN ('WEB_eCommSS_Q_CT') THEN 'Store Support'

                WHEN EnterpriseName IN ('WEB_UK.CS_Q_CT') THEN 'UK DIFM CS'

                WHEN EnterpriseName IN ('WEB_UK.TS_Q_CT') THEN 'UK TS'

                WHEN EnterpriseName IN ('WEB_TechSupport.RTS_Q_CT') THEN 'RTS'

                WHEN EnterpriseName IN ('WEB_UK.MODS_Q_CT') THEN 'UK MODS'

END AS GroupName

, ani, startdatetime, rank()over(partition by GroupName||ani order by startdatetime) AS ranking

                FROM 

                (SELECT TCD.datetime as startdatetime,

       TCD.CallTypeID, Call_Type.EnterpriseName,

                  TCD.ANI,

                   Person.FirstName||' '||Person.LastName as agent_name,

                  TCD.agentSkillTargetID                       

  FROM

                CiscoViewDB.Termination_Call_Detail as TCD

                INNER JOIN Call_Type ON Call_Type.CallTypeID = TCD.CallTypeID

                INNER JOIN Agent  ON (TCD.AgentSkillTargetID = Agent.SkillTargetID)

                INNER JOIN Person ON (Agent.PersonID = Person.PersonID)

                WHERE Person.FirstName IS NOT NULL and CAST(startdatetime AS DATE FORMAT 'mm/dd/yyyy') >=  CURRENT_date-200) UC2

                ) b

ON a.GroupName||a.ani = b.GroupName||b.ani AND a.ranking = b.ranking-1 

JOIN viewdb.date_lkp_v d

ON CAST(a.startdatetime AS DATE FORMAT 'mm/dd/yyyy') = d.day_date

LEFT OUTER JOIN

(SELECT Cal_Day_dt FROM VIEWDB.CAL_DATES_LKP_V where weekday_flag = 'Y' and holiday_flag IS NULL) e

on e.Cal_Day_dt between a.startdatetime and b.startdatetime

Group by 1,2,3,4,5,6,7,8,9,10