SUM CASE not working after migrating from SQL 2008r2 to SQL2014

[neeko1226]

I have a SUM(CASE expression that is not working when I run it in our new SQL 2014 (Windows Server 2012) environment. I run the same EXACT expression in our old SQL 2008r2 servers and it works. Any idea what might be causing the issue?

SELECT
AA_Category,
SUM(CASE WHEN (GHP_Tot+GHI_Tot+GRF_Tot+GCORF_Tot+GHO_Tot+GSB_Tot)<>0
THEN (GHP_Tot/(GHP_Tot+GHI_Tot+GRF_Tot+GCORF_Tot+GHO_Tot+GSB_Tot))*GHP_Rating*.5 ELSE 0 END) AS GHP_Weight
INTO #tbl_LendingScore_Temp
FROM #tbl_AARatings_Temp
GROUP BY AA_Category


Values in #tbl_AARatings_Temp
AA_Category = MYSTRING
GHP_Tot = 62
GHI_Tot = 5
GRF_Tot = 31
GCORF_Tot = 4
GHO_Tot = 0
GSB_Tot = 80
GHP_Rating = 5

CASE WHEN 62+5+31+4+0+80 <> 0 THEN ((62/(62+5+31+4+0+80))*5)*.5 ELSE 0

In SQL 2008r2 this returns 0.78680203 which is correct. In SQL 2014 it returns 0. I've played around with it and it seems to be that the value of the "GHP_Tot" after the THEN clause is using 0, but it should be 62. I'm lost. Any help would be appreciated greatly.

 

[fredericofonseca]

what is the definition of #tbl_AARatings_Temp?

preferably the output of tempdb..sp_helpdb #tbl_AARatings_Temp or the create table statement if it is created explicitly (e.g. not a select .. into )

in 2 of my servers (2008R2 and 2014) the following returns the same output regardless of which

if object_id('tempdb..#tbl_AARatings_Temp') is not null
    drop table #tbl_AARatings_Temp;
create table #tbl_AARatings_Temp
    ( AA_Category varchar(20)
    , GHP_Tot     decimal(20, 2)
    , GHI_Tot     decimal(20, 2)
    , GRF_Tot     decimal(20, 2)
    , GCORF_Tot   decimal(20, 2)
    , GHO_Tot     decimal(20, 2)
    , GSB_Tot     decimal(20, 2)
    , GHP_Rating  decimal(20, 2)
    )

insert into #tbl_AARatings_Temp
    select 'mystring'
         , 62
         , 5
         , 31
         , 4
         , 0
         , 80
         , 5

         select @@version, ((62/(62+5+31+4+0+80))*5)*.5
select AA_Category
     , sum(case
       when (GHP_Tot + GHI_Tot + GRF_Tot + GCORF_Tot + GHO_Tot + GSB_Tot) <> 0
           then (GHP_Tot / (GHP_Tot + GHI_Tot + GRF_Tot + GCORF_Tot + GHO_Tot + GSB_Tot)) * GHP_Rating * .5
       else 0
       end) as ghp_weight

from #tbl_AARatings_Temp
group by AA_Category


--Values in #tbl_AARatings_Temp
--AA_Category = MYSTRING	
--GHP_Tot	= 62
--GHI_Tot	= 5
--GRF_Tot	= 31
--GCORF_Tot = 4
--GHO_Tot = 0
--GSB_Tot	= 80
--GHP_Rating = 5

output of second select
mystring 0.851649


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

I think you are having an "Integer Math" problem.

SQL server will use integer math if all of your values are integers. Ex:

Select 5/10

Looking at your example, with numbers (and removing the case statement):

Select ((62/(62+5+31+4+0+80))*5)*.5

-- Replace values with data types.

Select ((int/(int+int+int+int+int+int))*int)*decimal

-- reducing....

Select ((int/(int))*int)*decimal
Select ((int/int)*int)*decimal
Select ((int)*int)*decimal
Select (int)*decimal
select decimal

As you can see, sql server will only convert to decimal at the end of the process. This occurs because of the parenthesis.

The easiest way to fix this problem is to force a conversion to decimal earlier in the process. Like this:

SELECT 
AA_Category,
SUM(CASE WHEN (GHP_Tot+GHI_Tot+GRF_Tot+GCORF_Tot+GHO_Tot+GSB_Tot)<>0 
THEN ([!]1.0 * [/!]GHP_Tot/(GHP_Tot+GHI_Tot+GRF_Tot+GCORF_Tot+GHO_Tot+GSB_Tot))*GHP_Rating*.5 ELSE 0 END) AS GHP_Weight
INTO #tbl_LendingScore_Temp
FROM #tbl_AARatings_Temp
GROUP BY AA_Category

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom