I have an interesting task at hand that I'm trying to figure out how to do.
Let's say I have the following data in a table:
Num1 Acct Amt Type1 Type2 AmtX AmtY AcctBadInd
X12 111 90 X 1 NULL NULL NULL
X12 222 -90 X 1 NULL NULL NULL
X12 333 90 X 1 NULL NULL NULL
Y33 111 75 Y 1 NULL NULL NULL
Y33 444 -75 Y 1 NULL NULL NULL
Z44 111 55 Y 1 NULL NULL NULL
Z44 111 55 Y 0 NULL NULL NULL
Z44 444 -65 Y 1 NULL NULL NULL
Below are a couple examples. Only caveat is that a given Num1 can have any number of records but always >= 2. So it could be 2,3,4,5 and the same logic would apply in all cases.
Verify that ABS(AMT) for all Type2=1 records is the same. If all 3 records have the same AMT, then SET AMTX=ABS(AMT) for that Num1. Alternatively, if Type1 was Y for X12 - then we would instead update AmtY = ABS(AMT)
Num1 = Y33 - In this case we again want to verify that ABS(AMT) is the same where Type2=1. If they are equal, then because Type1=Y, we would set AmtY =75
Num1=Z44 - in this case again verify that ABS(AMT) is the same for Type2=1. If they are not equal, then dont update AmtY, but rather set AcctBadInd = 1
End Result
Num1 Acct Amt Type1 Type2 AmtX AmtY AcctBadInd
X12 111 90 X 1 90 NULL NULL
X12 222 -90 X 1 90 NULL NULL
X12 333 90 X 1 90 NULL NULL
Y33 111 75 Y 1 NULL 75 NULL
Y33 444 -75 Y 1 NULL 75 NULL
Z44 111 55 Y 1 NULL NULL 1
Z44 444 -65 Y 1 NULL NULL 1
Z44 111 55 Y 0 NULL NULL NULL
I'm struggling with this, and I'm not expecting an answer but at least a hint or any help so I can get on my way. More so, if this is doable in a way that I imagine without writing god knows how much code.
If I understand correctly here is how you can do it:
with cte as (
select * , type2, max(abs(Amt)) minAmt, min(abs(Amt)) maxAmt, count(*) cnt
from table
group by Num1 , type2
);
update t1
set AmtX = case when cnt> 1 and Type2=1 and t1.type1 = 'X' and minAmt = maxAmt then minAmt end
, AmtY = case when cnt> 1 and Type2=1 and t1.type1 = 'Y' and minAmt = maxAmt then minAmt end
, AcctBadInd = case when cnt> 1 and Type2=1 and minAmt <> maxAmt then 1 end
from table t1
join cte on t1.Num1 = cte.Num1
and t1.type2 = cte.type2