Filtering records when a particular event occurs

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

​x
 
ID  |  PAYMT_TYPE  |          AMT |  DTE 123         REG                 100     JUN123         REG                 100     MAY123         REG                 100     APR123         REG                 100     MAR456         REG                 200     JUN456         ADD                 100     JUN456         REG                 200     MAY456         ADD                 100     MAY789         REG                 400     JUN789         REG                 400     MAY789         REG                 400     APR789         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'

 
SELECT *FROM PAYMENT_TABLEWHERE  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

 
ID  |  PAYMT_TYPE  |           AMT |  DTE 123         REG                 100     JUN123         ADD                 50       JUN123         REG                 100      MAY123         ADD                 50       MAY456         REG               200     JUN456        ADD                100     JUN456         REG               200     MAY456        ADD                100     MAY789        REG                400     JUN789        ADD                100     JUN789        REG                400     MAY789        ADD                100     MAY​

Can anyone help? I feel like I am missing something obvious

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

 
SELECT pt.*FROM PAYMENT_TABLE ptWHERE 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;

 
select id, dtefrom payment_tablewhere paymt_type in ('REG', 'ADD')group by id, dtehaving count(distinct paymt_type) = 2;​

Advertisement

Answer