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.
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.