---
schema_version: "1"
id: 1afb7825724fe4f2fe2802c7cfe5a39c6f4a50cb5b1e94425fe2df2a49d7ca1f
title: "Calculated Insight: Count Of Open Cases By Vehicle"
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/ind/264-0-0/auto_cicalculated_insight_count_of_open_cases_by_vehicle.md
canonical_url: https://help.salesforce.com/s/articleView?id=ind.auto_cicalculated_insight_count_of_open_cases_by_vehicle.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: 70f18dac658d75e09d01e74ccf0c5285cb44c9fea30b2165d602667a20305b81
description: The Count Of Open Cases By Vehicle calculated insight aggregates the number of open customer cases for a specific vehicle and groups them by year, quarter, and month. The insight considers unresolved cases and provides an understanding of customer case resolution.
products:
  - Automotive
release: "264"
---
# Calculated Insight: Count Of Open Cases By Vehicle

The Count Of Open Cases By Vehicle calculated insight aggregates the number of open customer cases for a specific vehicle and groups them by year, quarter, and month. The insight considers unresolved cases and provides an understanding of customer case resolution.

### Required Editions

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small edition"><colgroup><col></colgroup><tbody><tr class=""><td class="slds-cell-wrap" style="vertical-align:top;">Available in: <strong>Enterprise</strong>, <strong>Unlimited</strong>, and <strong>Developer</strong> Editions</td></tr></tbody></table>

Here’s a breakdown of the logic.

 
| Expression | Description |
| --- | --- |
| `SELECT COUNT(ssot__Case__dlm.ssot__Id__c) AS TotalCases__c` | Extracts the IDs of all cases, calculates the total count, and assigns it an alias TotalCases\_\_c. |
| `ssot__Vehicle__dlm.ssot__VehicleIdentificationNumber__c AS VIN__c` | Extracts the vehicle identification number from the Vehicle object and assigns it an alias VIN\_\_c. |
| `quarter(ssot__Case__dlm.ssot__CreatedDate__c) as Quarter__c` | Extracts the quarter information from the Created Date of the case records and assigns it an alias as Quarter\_\_c. |
| `year(ssot__Case__dlm.ssot__CreatedDate__c) as Year__c` | Extracts the year information from the Created Date of the case records and assigns it an alias as Year\_\_c. |
| `month(ssot__Case__dlm.ssot__CreatedDate__c) as Month__c` | Extracts the month information from the Created Date of the case records and assigns it an alias as Month\_\_c. |
| `FROM ssot__Case__dlm JOIN ssot__Vehicle__dlm ON (ssot__Case__dlm.ssot__AccountId__c = ssot__Vehicle__dlm.ssot__CurrentOwnerId__c)` | Joins the Vehicle and Case objects by matching the Account ID field on a case record to the Current Owner ID field on a vehicle record. |
| `WHERE ssot__Case__dlm.ssot__CaseStatusId__c = ‘New’` | Applies the filter condition to consider only the cases in New status. |
| `GROUP BY VIN__c, Quarter__c, Year__c, Month__c` | Groups the results by the vehicle identification number, month, quarter, and year. |