As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clientsâ€™ hosting easier.

Solved

Posted on 2007-03-27

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!

4 Comments

(

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.

Select 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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows. This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.

- SQL
- Microsoft SQL Server
- AWS
- Azure
- Docker
- *DevOps

Course of the Month8 days, 5 hours left to enroll

Join the community of 500,000 technology professionals and ask your questions.