CODEWITHSUNDEEP
sql-servercase
John Maygee
John Maygee

Reputation: 1

sum(case When.. AND) with multiple conditions not working

I am trying to run this sum query to produce two columns of Turnover one for TY and another for LY. However, I want any transactions flagged as Cancelled to appear as negatives:

select [Location],BR.BranchName, 
 sum(case when (FX.TransactionDateKey between '20171101' and '20181031') 
   and ([Action] = 'Cancelled') 
   then FX.CustomerValue*-1  else [CustomerValue] end) as [CustmVal TY],
 sum(case when (FX.TransactionDateKey between '20161101' and '20171031') 
   and ([Action] = 'Cancelled')
   then FX.CustomerValue*-1 else FX.CustomerValue*1 end) AS [CustmVal LY]
from [dbo].[FRX_Transactions] FX
inner join DWX_Branch BR on BR.BranchID=FX.[Location]
where FX.TransactionDateKey between '20161101' and '20181031' and BR.BusinessDivision = 'Retail'
and FX.[Action] in ('Trade','cancelled') and FX.Reason in ('Public','BBG','Overridesupplyrate') and FX.Operation in ('Add','Del')
group by FX.[Location],BR.BranchName, BR.BranchOpenDate,BR.BranchCloseDate,BR.ActiveStatus
order by BR.BranchName

However, when I run it I get similar data in both columns - it seems to ignore the date conditions. Please, what am I doing wrong? Is this case-when-statement with TWO conditions written wrong?

Any help would be HUGELY appreciated. Massive thanks!

Upvotes: 0

Views: 129

Answers (1)

Thom A
Thom A

Reputation: 95561

I think what you actually want for your CASE expression is:

CASE WHEN FX.TransactionDateKey BETWEEN '20171101' AND '20181031' THEN [CustomerValue] *
                                                                       CASE WHEN [Action] = 'Cancelled' THEN -1 ELSE 1 END
END

Upvotes: 1

Related Questions