Example: Cases Closed by Number of Searches

Use this example SQL for percent and total cases closed by number of searches.

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, ABS(SubQuery2.NumberOfSearches__c) AS NumberOfSearches__c, APPROX_COUNT_DISTINCT(<Cases__dlm.CaseId__c>) AS CasesClosedWithCount__c, APPROX_COUNT_DISTINCT(<Cases__dlm.CaseId__c>)/MAX(SubQuery1.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>) SubQuery1 ON <Cases__dlm.IsClosed__c> = SubQuery1.IsClosed__c LEFT JOIN (SELECT SASearchResults__dlm.RecordId__c AS RecordId__c, APPROX_COUNT_DISTINCT(SASearchResults__dlm.QueryId__c) AS NumberOfSearches__c FROM SASearchResults__dlm WHERE SASearchResults__dlm.RecordId__c IS NOT NULL GROUP BY SASearchResults__dlm.RecordId__c) SubQuery2 ON <Cases__dlm.CaseId__c> = SubQuery2.RecordId__c WHERE <Cases__dlm.IsClosed__c> = TRUE AND SubQuery2.RecordId__c IS NOT NULL GROUP BY <Cases__dlm.ClosedDate__c>, NumberOfSearches__c