Aggregate option in SQL Server CASE statement

问题:

I have the following data in a #temp table:

Id  code       Fname       CompanyId    FieldName         Value
----------------------------------------------------------------
465 00133   JENN WILSON       1           ERA              1573
465 00133   JENN WILSON       1           ESHIFTALLOW      3658
465 00133   JENN WILSON       1           NETPAY          51560

I want to do following operation i.e

• One Row will be addition on two columns i.e ERA + ESHIFTALLOW
• Other Row will be subtraction & addition on three columns i.e NETPAY - ERA + ESHIFTALLOW

I had tried using case statement in SQL Server.

This is the query

Select 
    Id, Code, Fname, CompanyID, Company, FieldNameNew,
    Sum(Value) Value 
Into 
    #temp2
from 
    (select 
         Id, Code, Fname, CompanyID, Company, Value,
         case 
            when FieldName in ('OT', 'EONCALLALLOW', 'ESHIFTALLOW', 'FOT', 'EUNIALLOW', 'ESECALLOW', 'ERA') 
               then 'OT+EONCALLALLOW+ESHIFTALLOW+FOT+EUNIALLOW+ESECALLOW+ERA'
            when FieldName in ('NETPAY') 
               then 'NETPAY-OT+EONCALLALLOW+ESHIFTALLOW+FOT+EUNIALLOW+ESECALLOW+ERA'      
            else FieldName 
         end, 
         FieldNameNew
     from 
         #temp) A
group by 
    Id, Code, Fname, CompanyID, Company, FieldNameNew

Using above query im able to get partial result

Id  code          Fname  CompanyId   FieldNameNew                                                   Value
465 00133   JENN WILSON   1       NETPAY-OT+EONCALLALLOW+ESHIFTALLOW+FOT+EUNIALLOW+ESECALLOW+ERA    51560  //INCORRECT Actual value should be (51560-5231) = 46329
465 00133   JENN WILSON   1       OT+EONCALLALLOW+ESHIFTALLOW+FOT+EUNIALLOW+ESECALLOW+ERA           5231 //CORRECT

Addition is working fine but while doing subtraction in NETPAY it is not working

NETPAY-OT+EONCALLALLOW+ESHIFTALLOW+FOT+EUNIALLOW+ESECALLOW+ERA value = 51560 //INCORRECT Actual value should be (51560-5231) = 46329

OT+EONCALLALLOW+ESHIFTALLOW+FOT+EUNIALLOW+ESECALLOW+ERA
Value= 5231 //CORRECT

回答1:

You have correct logic but wrong formula :

This is wrong way

**NETPAY-OT+EONCALLALLOW+ESHIFTALLOW+FOT+EUNIALLOW+ESECALLOW+ERA**

You must use

**NETPAY-OT-EONCALLALLOW-ESHIFTALLOW-FOT-EUNIALLOW-ESECALLOW-ERA**

Or

**NETPAY-(OT+EONCALLALLOW+ESHIFTALLOW+FOT+EUNIALLOW+ESECALLOW+ERA)**