November 19, 2007 at 2:28 am
Hello,
I am creating queries that create parcentages. Title and Percentage_Of hold descriptions of the business question and what the percentages are based on.
PEP for both Q05 and Q05b contain the number of records.
I have the following query
SELECT Q05b.Title, Q05b.Percentage_Of, CAST(CAST(Q05b.PEP AS Decimal)/CAST(Q05.PEP AS Decimal) *100 AS Decimal)AS PEP, Q05b.Quality_Date
FROM #Q05b_PEP Q05b
INNER JOIN #Q05_PEP Q05
ON Q05.Quality_Date = Q05b.Quality_Date
GO
For this one though both columns Im trying to calculate are 0 which means Im getting Divide by zero error encountered. Ive found some help in regards to NULLIF and tried putting this into the query above but I cant quite make it work.
If anyone could help figure out how to make the CAST(CAST(Q05b.PEP AS Decimal)/CAST(Q05.PEP AS Decimal) *100 AS Decimal)AS PEP work even if the fields contain zeros that would be great.
Thanks
Debbie
November 19, 2007 at 2:41 am
And after all that I managed to get it working anyway :w00t:
SELECT Q05b.Title, Q05b.Percentage_Of, CAST(CAST(NULLIF(Q05b.PEP,0) AS Decimal)/CAST(NULLIF(Q05.PEP,0) AS Decimal) *100 AS Decimal)AS PEP, Q05b.Quality_Date
FROM #Q05b_PEP Q05b
INNER JOIN #Q05_PEP Q05
ON Q05.Quality_Date = Q05b.Quality_Date
GO
DEBBIE
November 19, 2007 at 7:35 am
Though this way the entire expression will evaluate to NULL in the case you should try to divide by zero.
If this isn't the desired behaviour, the null can be converted to a 1 by ISNULL(NULLIF(myCol, 0), 1)
/Kenneth
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply