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
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 |