fluffybonkers fluffybonkers - 5 months ago 9
SQL Question

Why Does SQL Server Not Return 0 for Calculation

I have the following query:

select
(case
when try_convert(decimal(3, 1), DEC10assessmentData.writ_literatureReview) is not null
then cast(DEC10assessmentData.writ_literatureReview as decimal(3, 1))
else 0
end / 100 * 4
+ case
when try_convert(decimal(3, 1), DEC10assessmentData.writ_exposition) is not null
then cast(DEC10assessmentData.writ_exposition as decimal(3, 1))
else 0
end / 100 * 8
+ case
when try_convert(decimal(3, 1), DEC10assessmentData.writ_groupReport) is not null
then cast(DEC10assessmentData.writ_groupReport as decimal(3, 1))
else 0
end / 100 * 8
+ case
when try_convert(decimal(3, 1), DEC10assessmentData.writ_synthSummary) is not null
then cast(DEC10assessmentData.writ_synthSummary as decimal(3, 1))
else 0
end / 100 * 8
+ case
when try_convert(decimal(3, 1), DEC10assessmentData.writ_critEvaluation) is not null
then cast(DEC10assessmentData.writ_critEvaluation as decimal(3, 1))
else 0
end / 100 * 12) / 40 * 100
from
DEC10assessmentData


Why does it return
0.800000000
when all values in the selected columns are
NULL
?

It returns correct results when columns have scores.

Answer

I tried creating demo here but I am getting 0.0000, not 0.8000. Are you sure that all values are null?

Null Demo

If yes, the try running each case statement alone and see from which you are are not getting 0 in output.

So first run

select 
(case when try_convert(decimal(3,1), DEC10assessmentData.writ_literatureReview) 
is not null
   then 
cast(DEC10assessmentData.writ_literatureReview as decimal(3,1)
     ) else 0 
 end / 100 * 4
 )/ 40 * 100
from DEC10assessmentData

If this return only 0 then add another case statement and check further.