Select MaxMonthDate,MaxMonth,PassportsIssued,PassportVerifications,SkillVerifications, Case When PassportsIssued = 0 Then 'No Ratio Available' Else Cast((PassportVerifications + SkillVerifications) / PassportsIssued As Varchar(20)) End As Calculated From

(

SELECT

max(dbo.getmonthdate(t.TransDate)) As MaxMonthDate,

max(dbo.getmonth(t.TransDate)) As MaxMonth,

Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,

Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications,(Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS Calculated

From MyTable) Calculation

For example, doing a secondary select means that you can work with the totals without having to recalculate them.

(

SELECT

max(dbo.getmonthdate(t.Tra

max(dbo.getmonth(t.TransDa

Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,

Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications,(Sum(CA

From MyTable) Calculation

For example, doing a secondary select means that you can work with the totals without having to recalculate them.