MayoorPatel
asked on
How to Avoid Divide by Zero in a Calculated Column
I am trying to protect a divide by zero in my last column.
SELECT
max(dbo.getmonthdate(t.Tra nsDate)),
max(dbo.getmonth(t.TransDa te)),
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 SE 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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.