I am trying to protect a divide by zero in my last column.

SELECT

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

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

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

The problem is that

(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)

may sometimes be zero in which case I need to return "No Ratio Available"

Can Anyone help? I understand I may have to use a nested case statement or something!

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

The problem is that

(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)

may sometimes be zero in which case I need to return "No Ratio Available"

Can Anyone help? I understand I may have to use a nested case statement or something!

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialSelect MaxMonthDate,MaxMonth,Pass

, Case When PassportsIssued = 0 Then 'No Ratio Available'

Else Convert(varchar(30),passpo

End As Calculated

From

(

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

From MyTable) as X

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,

CASE WHEN t.TransType =1 THEN CAST((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 varchar(2000) ) ELSE 'No Ratio Available' END AS Calculated

FROM GetCardtypes(3) ct inner join

tblTransactions t

on ct.cardtypeID=t.cardTypeID

inner join tblUsers u on

u.PHID = t.IssuedToID

Can anyone help add this to the solutions you have provided.

Microsoft SQL Server

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

(

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.