I have the following dataset that lists payments made onto an account, the type of payment (either a regular payment, interest payment or additional payment) and the date of the payments.
ID | PAYMT_TYPE | AMT | DTE 123 REG 100 JUN 123 REG 100 MAY 123 REG 100 APR 123 REG 100 MAR 456 REG 200 JUN 456 ADD 100 JUN 456 REG 200 MAY 456 ADD 100 MAY 789 REG 400 JUN 789 REG 400 MAY 789 REG 400 APR 789 REG 400 MAR
Most accounts will simply have a REG payment made each month, however I want to pull out the ones that made both a REG and an ADD on the same date.
The query I have, below, will give me all the REG’s and ADDs but will not group these by the ID. So I end up with a bunch of REG only accounts.
SELECT *
FROM PAYMENT_TABLE
WHERE PAYMT_TYPE IN ('REG', 'ADD')
AND DTE BETWEEN '01-JUN-20' AND '01-JUN-21'
My ideal output would look like this :
ID | PAYMT_TYPE | AMT | DTE 123 REG 100 JUN 123 ADD 50 JUN 123 REG 100 MAY 123 ADD 50 MAY 456 REG 200 JUN 456 ADD 100 JUN 456 REG 200 MAY 456 ADD 100 MAY 789 REG 400 JUN 789 ADD 100 JUN 789 REG 400 MAY 789 ADD 100 MAY
Can anyone help? I feel like I am missing something obvious
Advertisement
Answer
You can use exists:
SELECT pt.*
FROM PAYMENT_TABLE pt
WHERE PAYMT_TYPE IN ('REG', 'ADD') AND
DTE BETWEEN '01-JUN-20' AND '01-JUN-21' AND
EXISTS (SELECT 1
FROM PAYMENT_TABLE pt2
WHERE pt2.ID = pt.ID AND
pt2.PAYMT_TYPE IN ('REG', 'ADD') AND
pt2.PAYMT_TYPE <> pt.PAYMT_TYPE
);
Or, if you only want the id/date combinations:
select id, dte
from payment_table
where paymt_type in ('REG', 'ADD')
group by id, dte
having count(distinct paymt_type) = 2;