I have a Patient table that is like this for account 999888 that only needs for the payment_amount that is not zero to sum up the payment_amount2:

Here is my query that I am trying to use:

select sum(payment_amount2) as payment_amount2 from patient
where payment_amount!=0
group by patient.account_number,patient.nrv

However similarly I might have account 991991 that only has a $0 payment_amount and I need to sum up payment_amount2.

Here is the query for that:

select sum(payment_amount2) as payment_amount2 from patient
where payment_amount=0
group by patient.account_number,patient.nrv

I also may have a scenario like this for account 989111 where the payment_amount can be canceled by another payment_amount if they are of equal amount which then I cannot include that payment_amount2 in the sum:

My question is how do I combine the two queries?

CodePudding user response：

You can just divide the business cases using case expressions:

with cte  (account, NRV ,Code   ,Payment_amount,    payment_amount2) 
as (
select '999888', 'Z01'  ,'1111111'  ,99,    100
union all select '999888' ,'Z01'    ,'1111111', 0   ,150
union all select '999888', 'Z01'    ,'1111111', 44  ,200
  union all select '999888', 'Z01'  ,'1111111', -44 ,155
  union all select '991991', 'Z01'  ,'1111111', 0   ,150
)
,cte2 as (
select 
account, 
NRV,
Code,
abs(payment_amount) abs,
count(case when payment_amount > 0 then 1 end) as p,
count(case when payment_amount < 0 then 1 end) as n,
sum(payment_amount2) as payment_amount2
from cte
group by abs(payment_amount),account, NRV,Code
),
cte3 as (
select 
*,case when abs = 0 then 0
         when p = n then 0
         when p > n then p - n
         when p < n then n - p end as pn
from cte2
  ), cte4 as (
  select 
  account,nrv,code,
  case when count(*) over (partition by account) = 1 
  then payment_amount2
  when pn <> 0 then payment_amount2 end as payment_amount2
  from cte3
    )
    select account,nrv,sum(payment_amount2) as payment_amount2
    from cte4
    group by account,nrv

CodePudding user response：

Break it down by most restrictive to least restrictive first. This will give you the flow. Then, build out the case, again, from most restrictive to least restrictive.

I am not sure about the last case, so will build it out with the first two cases.

case when account = '999888' and payment_amount != 0 then payment_amount
else when account = '991991' and payment_amount = 0 then payment_amount2
else ...
else 0 --the default case.
end 

We then throw that in a sum.

sum(
    case when account = '999888' and payment_amount != 0 then payment_amount
    else when account = '991991' and payment_amount = 0 then payment_amount2
    else ...
    else 0
end) as payment_amount2

Just make sure it is readable, large cases make it hard to maintain the query if you don't keep it readable.