---
schema_version: "1"
id: 5b69126d66aaabc7598e06fc878569b819fe5fe5093a5a904c0dcf48c0219af9
title: "Example: Cases Closed by Number of Searches"
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/ai/264-0-0/search_es_case_closure_exp2.md
canonical_url: https://help.salesforce.com/s/articleView?id=ai.search_es_case_closure_exp2.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: 0aa5bdfefd0e868a56a2670afb0c25e87de560bb720dbb164bcec7ae323024ef
description: Use this example SQL for percent and total cases closed by number of searches.
products:
  - Cross Cloud
release: "264"
---
# 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
```

#### See Also

- [Create a Case Closure Calculated Insight](https://help.salesforce.com/s/articleView?id=ai.search_es_SA_case_closure_CI.htm&release=264.0.0&type=5 "Create a calculated insight (CI) with case-related metrics by using example SQL expressions. Use the CI to see how many interactions happen before the case is closed, to analyze case-related metrics, and to build dashboards.")