SQLTeam.com | Weblogs | Forums

Differentiate complient and non complient call from table data


#1

I have this information following is Table
SELECT
PERSON_ID, CALLCENTER_Call_STATUS_ID,STATUS_CHANGED_TIME,LAST_STATUS_CHANGED_TIME,Call_CREATED_DATETIME,Call_CLOSURE_DATETIME,LATEST_RESPOND_BY_DATETIME,MAX_RESPONSE_TIME,SUSPEND_DURATION,SECONDARY_SUSPEND_DURATION,TIME_IN_QUEUE,Call_CLOSE_TIME_IN_MINUTES
FROM CALLACTIVITYDETAILS

  1.   call(1111) has generated at 6:40 AM. person suspended call after 15 Mins, so the time remaining is 5 Mins. Agent Person suspended that same call after 12 Mins, so the time remaining is 3 Mins and then person Closed this call At 7:09 AM. As the time remaining from “must respond within time” is 1 Min. It means the person closed the call within configured SLA (it was 20 Mins). The Ticket is Complaint 
    
  2.   Another call(2222) has generated at 7:00 AM. person suspended call after 16 Mins so the time remaining is 4 Mins and then  person Closed this call At 7:32 AM. As the SLA time has been passed so the Ticket is Non-Complaint
    

This is the Query i have done to calculate but something is wrong

SELECT @TOTAL_NC_Call =count( distinct CCAAD.CALL_CENTER_ALERT_Call_ID)
FROM CALLACTIVITYDETAILS CCAAD
inner join @DATASET d on CCAAD.AGENT_ID = d.ID
where CCAAD.CALL_CREATED_DATETIME between @START_DATETIME and @END_DATETIME
AND (CASE WHEN CALL_CLOSE_TIME_IN_MINUTES IS NOT NULL AND (TOTAL_SUSPENDED_TIME_IN_MINUTES <= (SUSPEND_DURATION * SUSPEND_COUNT))
THEN (CALL_CLOSE_TIME_IN_MINUTES - TOTAL_SUSPENDED_TIME_IN_MINUTES) - MAX_RESPONSE_TIME
WHEN CALL_CLOSE_TIME_IN_MINUTES IS NOT NULL AND (TOTAL_SUSPENDED_TIME_IN_MINUTES > (SUSPEND_DURATION * SUSPEND_COUNT))
THEN MAX_RESPONSE_TIME - (CALL_CLOSE_TIME_IN_MINUTES - (SUSPEND_DURATION * SUSPEND_COUNT))

WHEN CALL_CLOSE_TIME_IN_MINUTES IS NULL  AND (TOTAL_SUSPENDED_TIME_IN_MINUTES < (SUSPEND_DURATION * SUSPEND_COUNT))  
THEN MAX_RESPONSE_TIME - (DATEDIFF(MINUTE, CALL_CREATED_DATETIME, DBO.CONVERTFROMUTC(GETUTCDATE(), @TIME_ZONE_NAME)) - TOTAL_SUSPENDED_TIME_IN_MINUTES)  
WHEN CALL_CLOSE_TIME_IN_MINUTES IS NULL  AND (TOTAL_SUSPENDED_TIME_IN_MINUTES > (SUSPEND_DURATION * SUSPEND_COUNT))  
THEN   (DATEDIFF(MINUTE, CALL_CREATED_DATETIME, DBO.CONVERTFROMUTC(GETUTCDATE(), @TIME_ZONE_NAME)) - (SUSPEND_DURATION * SUSPEND_COUNT)) -MAX_RESPONSE_TIME 

END > 0)


#2

You haven't told us what you are trying to do. Also, please supply DDL and sample data so we know what you are asking