CODEWITHSUNDEEP
sqlsql-servercountpercentage
RIvalZero
RIvalZero

Reputation: 11

SQL - Percentage of one count by the other

I am trying to get the percentage of the first count by the second count. In other words, I want to do NbFirstCallResolutions / TotalCases * 100. 

SELECT 

(SELECT 
COUNT([Incident].IncidentId)
FROM [Incident],[CaseResolution]
WHERE [Incident].IncidentId = [CaseResolution].RegardingObjectId
AND [CaseResolution].FirstCallResolution = 1
AND [Incident].CreatedOn >= @Parameter_StartDate 
AND [Incident].CreatedOn <= DATEADD(day,1,@Parameter_EndDate)
) AS NbFirstCallResolutions,

(SELECT 
COUNT([Incident].IncidentId) 
FROM [Incident]
WHERE [Incident].CreatedOn >= @Parameter_StartDate 
AND [Incident].CreatedOn <= DATEADD(day,1,@Parameter_EndDate)
) AS TotalCases

--SELECT NbFirstCallResolutions / TotalCases * 100.0

Not sure how to approach this one...

Upvotes: 1

Views: 70

Answers (1)

crthompson
crthompson

Reputation: 15865

Your query should be able to be consolidated like this:

SELECT 
  SUM(coalesce([CaseResolution].FirstCallResolution, 0) / 
  COUNT([Incident].IncidentId) * 100
FROM 
  [Incident]
  left join [CaseResolution] on 
           [Incident].IncidentId = [CaseResolution].RegardingObjectId 
           AND [CaseResolution].FirstCallResolution = 1
WHERE 
  [Incident].CreatedOn >= @Parameter_StartDate 
  AND [Incident].CreatedOn <= DATEADD(day,1,@Parameter_EndDate

Notice that I summed the 1 value of FirstCallResolution, leaving the nulls as 0. This would be equivalent of counting the rows.

Be sure to check your order of operations as I believe the multiply will occur first, followed by the divide.

Upvotes: 1

Related Questions