All Forums Database
Jonathan.S 1 post Joined 08/16
08 Aug 2016
Looking to count the first event in a colum

Hi,
I am trying to identify a repeat caller as deliniated by group code I have a data set as that has a colum of event id's (that can potentialy be duplicates) a colum of interaction id's that are unique, a date time field. and a subscription ID
I have been able to identify the the calls at an agragate level but am having dificulty with the deliniation. currently the query looks as follows.

SELECT 
 MONTH_EVENT
 , Call_Cntr_Call_Grp_Cd
 , call_cnt
 , REPEAT_CNT
 , CAST(REPEAT_CNT AS DECIMAL(18,4)) /CAST(call_cnt AS DECIMAL(18,4)) AS REPEAT_RATE
FROM 
  
(
SELECT 
month_event
, Call_Cntr_Call_Grp_Cd
, Event_Start_DtTm
, COUNT( DISTINCT  call_cntr_cntct_event_id) AS call_cnt
, COUNT( DISTINCT CASE WHEN prior7days = 1 THEN call_cntr_cntct_event_id END) AS REPEAT_CNT

FROM
    
    /**************************   flag repeat call customer in the past 7 rolling days *********/
    
    
      (
         SELECT  
          x.subs_id 
          , EXTRACT(YEAR FROM x.date_event) AS  year_event 
          , X.month_event
          , X.Call_Cntr_Call_Grp_Cd
          , X.Event_Start_DtTm
          , X.call_cntr_cntct_event_id
          , MAX(CASE WHEN Y.date_event IS NULL THEN 0 ELSE 1 END )AS prior7days
                                               
          FROM
                TEMP_DB.JSILV_REPEAT    X
                   LEFT JOIN 
                     TEMP_DB.JSILV_REPEAT Y      
                  ON X.subs_id = Y.subs_id  AND y.date_event BETWEEN x.date_event-6 AND x.date_event 
                   AND x.call_cntr_cntct_event_id <> y.call_cntr_cntct_event_id
                   AND y.event_start_dttm < x.event_start_dttm
                   AND y.Call_Cntr_Call_Grp_Cd = x.Call_Cntr_Call_Grp_Cd
          
            GROUP BY 1,2,3,4,5,6
           )    x
                           
      GROUP BY 1,2
     
) y

can anyone help with how i might achieve the deliniation to the group code without the duplicate values?
 
thanks
Jon
 

You must sign in to leave a comment.