CODEWITHSUNDEEP
sqloracle-database
Mohsin Alvi
Mohsin Alvi

Reputation: 21

Need to display records which has positive and negative value

I have the following data in my table 

ID     code     date        amount   health_plan
111    A123     20170101     10      BH
111    A123     20100101    -10      BH
311    A124     20170712     20      CVA
311    A124     20170712    -20      CVA
311    A124     20170712     20      CVA
311    A124    20180201      55      CVA

I create a query to display those records who has both positive and negative values for the same ID, code and date. After running the following query I am expecting this type of output.

111    A123    20170101      10      BH
111    A123    20100101     -10      BH
311    A124    20170712      20      CVA
311    A124    20170712     -20      CVA

WITH amt_cur
        AS (SELECT DISTINCT first_name,
                            last_name,
                            birth_date,
                            gender,
                            health_plan,
                            id,
                            code,
                            date,
                            amount
            FROM   table_a
            WHERE  (id,
                    code,
                    date,
                    health_plan) IN
                      (SELECT a.id,
                              a.code,
                              a.date,
                              a.health_plan
                       FROM   table_a a
                              JOIN table_a b
                                 ON     b.health_plan = a.health_plan
                                    AND b.id = a.id
                                    AND b.date = a.date
                                    AND b.code = a.code
                                    AND b.amount <> a.amount
                                    AND (a.amount > 0 AND b.amount < 0)))
SELECT *
FROM   amt_cur
ORDER BY id, code, date;

But I am getting the following output

111    A123     20170101      10       BH
111    A123     20100101     -10       BH
311    A124     20170712      20       CVA
311    A124     20170712     -20       CVA
311   A124      20170712      20       CVA

Can someone help to achieve the results?

Upvotes: 0

Views: 2090

Answers (3)

Raphael Hinterndorfer
Raphael Hinterndorfer

Reputation: 157

WITH amt_cur
        AS (SELECT MAX(first_name) first_name,
                            MAX(last_name) last_name,
                            MAX(birth_date) birth_date,
                            MAX(gender) gender,
                            health_plan,
                            id,
                            code,
                            "date",
                            amount
            FROM   table_a
            WHERE  (id,
                    code,
                    "date",
                    health_plan,
                    ABS(amount)) IN
                      (SELECT distinct a.id,
                              a.code,
                              a."date",
                              a.health_plan,
                              ABS(a.amount)
                       FROM   table_a a
                              JOIN table_a b
                                 ON     b.health_plan = a.health_plan
                                    AND b.id = a.id
                                    AND b."date" = a."date"
                                    AND b.code = a.code
                                    AND ABS(b.amount + a.amount) < 0.00001
                                    AND (a.amount > 0 AND b.amount < 0))
  GROUP BY health_plan, id, code, "date", amount
                                    )
SELECT *
FROM   amt_cur
ORDER BY id, code, "date";

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

How about simply using exists:

select t.*
from t
where exists (select 1 from t t2 where t2.id = t.id and t2.amount < 0) and
      exists (select 1 from t t2 where t2.id = t.id and t2.amount > 0) ;

Upvotes: 2

wolfrevokcats
wolfrevokcats

Reputation: 2100

This query retrieves data according to your rules:

with t as (
   select a.* , count(distinct a.amount)over(partition by a.id,a."DATE",a.code,a.health_plan) "count"
    , sum (a.amount) over(partition by a.id,a."DATE",a.code,a.health_plan) "sum"
   from table_a a
)
select a.id,a.code,a."DATE",a.amount
from t a  where "count">1 and "sum"=0
;

Currently it produces nothing, because none of your data matches the rules.
The first two rows have different dates, the three next has sum !=0 , and the last does not have a pair.

Upvotes: 0

Related Questions