Case statement with multiple value and sum amount

1

Hi, How do you do a case statement for this. What's wrong with my query.

select

CONVERT(VARCHAR(10), CONVERT(DATE, ljtr.[TRANSDATE], 101), 101) as 'TRANSDATE',

ljtr.[VOUCHER],
ljtr.[ACCOUNTNUM],
ljtr.[TXT],
ljtr.[CURRENCYCODE],
sum(ljtr.[AMOUNTC]) as 'Amount Current',
sum(ljtr.[AMOUNT]) as 'Amount',

CASE sum(ljtr.[AMOUNT])
WHEN sum(ljtr.[AMOUNT])>0
AND
ljtr.[ACCOUNTNUM] in ('B30001','B30301')
,THEN 'Accrual' ELSE 'Reversal'
END AS 'Activity'

,ljtr.[DIMENSION3_] as 'Cost Center',

ljtr.[JOURNALNUM]

from [dbo].[abcd] ljtr

where ljtr.[TRANSDATE] >= '01/01/2020'
group by
ljtr.[VOUCHER],
ljtr.[ACCOUNTNUM],
ljtr.[TXT],
ljtr.[CURRENCYCODE],
ljtr.[DIMENSION3_],

ljtr.[JOURNALNUM],
CONVERT(VARCHAR(10), CONVERT(DATE, ljtr.[TRANSDATE], 101), 101)

2

You have mixed both types of CASE expression. There is the simple CASE expression and the searched CASE expression:

-- Syntax for SQL Server and Azure SQL Database  
  
Simple CASE expression:   
CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END   
Searched CASE expression:  
CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

You should be able to do this:

CASE WHEN sum(ljtr.[AMOUNT] > 0
      AND ljtr.[ACCOUNTNUM] IN ('B30001','B30301')
     THEN 'Accrual'
     ELSE 'Reversal'
 END AS Activity
1 Like
3

Thanks Jeff!