Example: Cases Closed by Interaction Type

Use this example SQL for percent and total cases closed by interaction type.

In this example, replace the bolded text with the API field names of your case closure DMO.

SELECT <Cases__dlm.ClosedDate__c> AS ClosedDate__c, SASearchInteractions__dlm.InteractionType__c AS InteractionType__c, APPROX_COUNT_DISTINCT(<Cases__dlm.CaseId__c>) AS CasesClosedWithInteraction__c, APPROX_COUNT_DISTINCT(<Cases__dlm.CaseId__c>)/MAX(SubQuery.TotalCasesClosed__c) * 100 AS PercentOfCasesClosed__c FROM <Cases__dlm> LEFT JOIN (SELECT <Cases__dlm.IsClosed__c> AS IsClosed__c, APPROX_COUNT_DISTINCT(<Cases__dlm.CaseId__c>) AS TotalCasesClosed__c FROM <Cases__dlm>  WHERE <Cases__dlm.IsClosed__c> = TRUE GROUP BY <Cases__dlm.IsClosed__c>) SubQuery on <Cases__dlm.IsClosed__c> = SubQuery.IsClosed__c LEFT JOIN SASearchResults__dlm ON <Cases__dlm.CaseId__c> = SASearchResults__dlm.RecordId__c LEFT JOIN SASearchInteractions__dlm ON SASearchResults__dlm.ResultId__c = SASearchInteractions__dlm.ResultId__c WHERE <Cases__dlm.IsClosed__c> = TRUE AND SASearchResults__dlm.RecordId__c IS NOT NULL AND SASearchInteractions__dlm.ResultId__c IS NOT NULL GROUP BY <Cases__dlm.ClosedDate__c>, SASearchInteractions__dlm.InteractionType__c