Find all transactions with a negative value a corresponding positive value and their balance-CodePudding

I have a table for which every user (number_id) there is a transaction.

I want to check that for each user that has a negative value, there is also a positive value (regardless if they don't match). If there is only a positive value, and no negative value, that's ok. But if there is a negative value, there should always be a positive value associated to that number_id for that item_id.

For example, for user 121198, his balance would be 20 (since 40-20). I would like to check that all balances are 0 or greater. For user 121200, he has no negative value only positive, this is correct, and his balance would be 10, However, for user 121101, he has only a negative balance, which should not be the case. His balance for this user would be -10, which should never happen.

Is there I way I can check these scenarios? For every user that has a negative value, there is a positive value, and also check the balance difference.

CREATE TABLE TBL_A
(
    number_id varchar(50), 
    item_id varchar(50), 
    amount int
    date datetime
);

INSERT INTO TBL_A
VALUES (121144, '1232p92nx', '-10','2021-10-08'),
       (121144, '1232p92nx', '10','2021-10-09'),
       (121111, '1232p92nx', '20','2021-10-07'),
       (121111, '1232p92nx', '-20','2021-10-08'),
       (121122, '1232p92nx', '30','2021-10-10'),
       (121122, '1232p92nx', '-15','2021-10-12'),
       (121198, '1232p92nx', '40','2021-10-11'),
       (121198, '1232p92nx', '-20','2021-10-12'),
       (121200, '1232p92nx', '10','2021-10-12'),
       (121101, '1232p92nx', '-15','2021-10-15');

CodePudding user response：

I'm not entirely clear on the desired output, but you can identify number_id with negative amounts and no corresponding positive amount that gets the sum out of the negative range like this:

select NUMBER_ID, min(AMOUNT) MINBAL, max(AMOUNT) MAXBAL, sum(AMOUNT) SUMBAL 
from TBL_A group by NUMBER_ID having SUMBAL < 0;

CodePudding user response：

I'd recommend creating a summary table/view/query by user & item using something like the following, then querying the summary for the scenarios you'd like.

select number_id as user_id, item_id, 
     sum(amount) balance, 
     sum(CASE when amount < 0 THEN 1 else 0 END) has_neg_amount, 
     sum(CASE when amount >= 0 THEN 1 else 0 END) has_pos_amount
from tbl_a
group by 1, 2
order by 1, 2;

an example of the summary in use

--example:  all records where there are more negative recs than positive recs
with summary_view as (
  select number_id as user_id, item_id, 
       sum(amount) balance, 
       sum(CASE when amount < 0 THEN 1 else 0 END) has_neg_amount, 
       sum(CASE when amount >= 0 THEN 1 else 0 END) has_pos_amount
  from tbl_a
  group by 1, 2
  order by 1, 2
  )
select * from summary_view where has_neg_amount > has_pos_amount;

I hope this helps...Rich

p.s. If this (or another) answer helps you, please take a moment to "accept" the answer that helped by clicking on the check mark beside the answer to toggle it from "greyed out" to "filled in".