How to Select ABS(Value) when a condition is being met in SQL Server 2014?

Question

When an Account is 'PrePaid' (PlanCode = P0100) then the balance owing is in negative (eg; -100 if they are owing $100.
When an Account is 'PostPaid' (PlanCode = P0200) then the balance owing is positive (eg; 100 if they are owing $100)
Some PrePaid accounts are owed money to and hence show balance as positive (eg; 50 would mean that we owe them $50)
Some PostPaid Accounts are owed money too (as in they overpaid) and show balance as negative (eg; -50 would mean that we owe them $50)

I want to generate a report that shows all accounts that owe us $5 or more.

My code is wrong, but perhaps my logic is right?

SELECT A.[AccountId]
      ,A.[AccountNumber]
      ,CASE WHEN P.PlanCode = 'P0100' THEN 'PrePaid'
        WHEN P.PlanCode = 'P0200' THEN 'PostPaid'
       END PlanType
      ,CASE WHEN P.PlanCode = 'P0100' THEN (Select ABS(CurrentBalance) FROM Account WHERE CurrentBalance < 0)
        WHEN P.PlanCode = 'P0200' THEN (Select [CurrentBalance] FROM Account WHERE CurrentBalance > 0)
       END [CurrentBalance]

FROM [GEB_DWH].[dbo].[Account] A
INNER JOIN AccountPlan P ON A.AccountId = P.AccountId

WHERE A.CurrentBalance >= 5

ORDER BY CurrentBalance DESC

EDIT:
I've also tried:

SELECT A.[AccountId]
      ,A.[AccountNumber]
      ,CASE WHEN P.PlanCode = 'P0100' THEN 'PrePaid'
        WHEN P.PlanCode = 'P0200' THEN 'PostPaid'
       END PlanType
      ,ABS([CurrentBalance])[CurrentBalance]
      --,CASE WHEN P.PlanCode = 'P0100' THEN (Select ABS(CurrentBalance) FROM Account WHERE CurrentBalance < 0)
        --WHEN P.PlanCode = 'P0200' THEN (Select [CurrentBalance] FROM Account WHERE CurrentBalance > 0)
      -- END [CurrentBalance]

FROM [GEB_DWH].[dbo].[Account] A
INNER JOIN AccountPlan P ON A.AccountId = P.AccountId

WHERE ((P.PlanCode = 'P0100' AND (A.CurrentBalance)  < 0) OR (P.PlanCode = 'P0200' AND (A.CurrentBalance)  > 0))
  AND A.CurrentBalance > 5

ORDER BY CurrentBalance DESC

score 3 · Accepted Answer · answered Dec 04 '17 at 19:25

3

If I understand correctly:

WHERE (A.PlanCode = P0100 AND A.CurrentBalance >= 5) OR
      (A.PlanCode = P0200 AND A.CurrentBalance <= -5)

answered Dec 04 '17 at 19:25

Gordon Linoff

1,242,037
58
646
786

I had tried something similar earlier. But the records didn't match. It didn't show a Prepaid Account that owed a large sum of money. I also tried your code. – user1777929 Dec 04 '17 at 19:31
I'm sorry. I used your code and it worked. Had to make slight changes to suit my data. – user1777929 Dec 04 '17 at 19:36

How to Select ABS(Value) when a condition is being met in SQL Server 2014?

1 Answers1