Is there a function that I could use in my “Select” or “Where” that would keep rows that cancel each other out from pulling into the extract?

Question

I have a script that pulls back charges for services. Sometimes a service is charged, then cancelled as shown in screenshot below. I was wondering if there is a function that I could use in my “Select” or “Where” that would keep those rows that cancel each other out from pulling into the extract?:

Code:

SELECT
 c.service_dt_tm "Service Date",
 c.item_price,
 c.charge_type_cd

FROM
 charge c,

 customer e

WHERE
 c.bill_item_id = 363803 AND
 c.charge_description = 'Oil Change' AND
 c.customer_id = e.customer_id AND

 e.active_ind = 1 AND
 e.customer_type_cd IN (9489, 9480, 9488) AND
 e.garage_loc_cd = 8473

Things I have tried but have not had any luck:

“Case” in the “Where” clause

“Match_Recognize”

Any and all help, directions, suggestions, guidance, references greatly appreciated.

Thanks, Roland

Answer 1

Count the number of rows of each type and filter out the rows where both types exist:

SELECT service_dt_tm AS "Service Date",
       item_price,
       charge_type_cd
FROM   (
  SELECT c.service_dt_tm,
         c.item_price,
         c.charge_type_cd,
         ROW_NUMBER() OVER (
           PARTITION BY c.customer_id,
                        c.service_dt_tm,
                        ABS(c.item_price),
                        c.charge_type_cd
           ORDER BY     NULL
         ) AS rn,
         LEAST(
           COUNT(CASE c.charge_type_cd WHEN 1577 THEN 1 END) OVER (
             PARTITION BY c.customer_id, c.service_dt_tm, ABS(c.item_price)
             ORDER BY NULL
           ),
           COUNT(CASE c.charge_type_cd WHEN 1576 THEN 1 END) OVER (
             PARTITION BY c.customer_id, c.service_dt_tm, ABS(c.item_price)
             ORDER BY NULL
           )
         ) AS num_matched
  FROM   charge c
         INNER JOIN customer e
         ON c.customer_id = e.customer_id
  WHERE  c.bill_item_id = 363803
  AND    c.charge_description = 'Oil Change'
  AND    e.active_ind = 1
  AND    e.customer_type_cd IN (9489, 9480, 9488)
  AND    e.garage_loc_cd = 8473
)
WHERE  rn > num_matched;

Which, for the sample data:

CREATE TABLE charge (customer_id, service_dt_tm, item_price, charge_type_cd, bill_item_id, charge_description) AS
  SELECT 1, TO_DATE('1970-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), 2.15, 1577, 363803, 'Oil Change' FROM DUAL UNION ALL
  SELECT 1, TO_DATE('1970-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), -2.15, 1576, 363803, 'Oil Change' FROM DUAL UNION ALL
  SELECT 1, TO_DATE('1970-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), 2.15, 1577, 363803, 'Oil Change' FROM DUAL UNION ALL
  SELECT 1, TO_DATE('1970-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), 2.15, 1577, 363803, 'Oil Change' FROM DUAL;

CREATE TABLE customer (customer_id, active_ind, customer_type_cd, garage_loc_cd) AS
  SELECT 1, 1, 9489, 8473 FROM DUAL;

Outputs:

Service Date	ITEM_PRICE	CHARGE_TYPE_CD
1970-01-01 12:34:56	2.15	1577
1970-01-01 12:34:56	2.15	1577

fiddle