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
| Available in: Enterprise, Unlimited, and Developer Editions |
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. |