Ejemplo: Casos cerrados por número de búsquedas

Utilice este SQL de ejemplo para porcentaje y total de casos cerrados por número de búsquedas.

En este ejemplo, sustituya el texto en negrita por los nombres de campo de API del DMO de cierre de su caso.

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