Subtracting a column based on another column

Question

This table consists of transaction data, where each row is one transaction exchange. Main data to collect: average amount of years it takes users to switch from checks to credit cards when paying.

I'm trying to subtract the year a person first used a check with the year he first used a credit card to the same receiving bank account. Example data is attached below. This query is freaking complicated and I'm wondering if I should/can even do this, but this is what I got so far

SELECT 
    ID,
    BankAcc#,
FROM table
GROUP BY
    ID,
    BankAcc#,
    TransYear,
    Method
ORDER BY 
    ID,BackAcc#,TransYear ASC

Example table (sorry couldn't embed the photo since I'm new)

My idea was to group ID-Bank#-TransYear-Method into a single row, with the TransYear being the earliest year of that Method, by using ORDER BY ASC LIMIT 1. Problems I'm running into:

Oracle doesn't support LIMIT after the ORDER BY clause. I tried using OFFSET or FETCH but it didn't work for some reason after the ORDER BY clause. I have Oracle 12.9.0.71 so I theoretically should have that function, but nope. I also tried WHERE rownum=1 but it limits all my results to 1 instead of limiting the ID-Bank-Year-Method group to 1.
Even if I do manage to get rows of the correct data, I still don't know how I can subtract the year values of check against card. Issue is that the data is in the same column and I need to distinguish it with another column before subtracting the year values.

Any thoughts? Appreciate any help, especially since this is really complicated.

MT0 · Accepted Answer · 2018-06-13T01:15:11.440

Two methods - one using MIN and CASE and the other using MIN and PIVOT

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( PersonId, BankAcc, TransactionNo, TransYear, method) as
  select 1, 10, 1,  2011, 'check' from dual union
  select 1, 10, 5,  2012, 'card'  from dual union
  select 2, 11, 7,  2012, 'check' from dual union
  select 2, 15, 10, 2012, 'check' from dual union
  select 2, 15, 11, 2014, 'card'  from dual union
  select 2, 15, 14, 2016, 'card'  from dual union
  select 2, 19, 15, 2009, 'check' from dual union
  select 2, 19, 16, 2015, 'card'  from dual union
  select 3, 20, 25, 2017, 'check' from dual union
  select 3, 21, 34, 2015, 'check' from dual union
  select 3, 21, 51, 2017, 'card'  from dual;

Query 1:

SELECT   PersonID,
         BankAcc,
         MIN( CASE method WHEN 'card' THEN TransYear END )
           - MIN( CASE method WHEN 'check' THEN TransYear END ) AS diff
FROM     table_name
GROUP BY PersonID,
         BankAcc
ORDER BY PersonID,
         BankAcc

Results:

| PERSONID | BANKACC |   DIFF |
|----------|---------|--------|
|        1 |      10 |      1 |
|        2 |      11 | (null) |
|        2 |      15 |      2 |
|        2 |      19 |      6 |
|        3 |      20 | (null) |
|        3 |      21 |      2 |

Query 2:

SELECT PersonId,
       BankAcc,
       crd - chk AS diff
FROM   ( SELECT PersonId, BankAcc, TransYear, method FROM table_name )
PIVOT( MIN( transyear ) FOR method IN ( 'check' AS chk, 'card' AS crd ) )
ORDER BY PersonID, BankAcc

Results:

| PERSONID | BANKACC |   DIFF |
|----------|---------|--------|
|        1 |      10 |      1 |
|        2 |      11 | (null) |
|        2 |      15 |      2 |
|        2 |      19 |      6 |
|        3 |      20 | (null) |
|        3 |      21 |      2 |

It worked! You're insane thank you. I'll refine up the search a bit more but that MIN CASE logic was critical. — Yaga, Jun 13 '18 at 16:09

score 0 · Answer 2 · answered Jun 12 '18 at 22:00

This query returns the result you posted; see if it is really OK.

SQL> with test (person_id, acc, trans, tyear, method) as
  2    (select 1, 10, 1,  2011, 'check' from dual union
  3     select 1, 10, 5,  2012, 'card'  from dual union
  4     select 2, 11, 7,  2012, 'check' from dual union
  5     select 2, 15, 10, 2012, 'check' from dual union
  6     select 2, 15, 11, 2014, 'card'  from dual union
  7     select 2, 15, 14, 2016, 'card'  from dual union
  8     select 2, 19, 15, 2009, 'check' from dual union
  9     select 2, 19, 16, 2015, 'card'  from dual union
 10     select 3, 20, 25, 2017, 'check' from dual union
 11     select 3, 21, 34, 2015, 'check' from dual union
 12     select 3, 21, 51, 2017, 'card'  from dual
 13    ),
 14  inter as
 15    (select person_id, acc, trans, tyear, method,
 16       first_value(tyear) over (partition by person_id, acc, method order by trans) fv
 17       from test
 18    )
 19  select person_id, acc, max(fv) - min(fv) diff
 20  from inter
 21  group by person_id, acc
 22  having count(distinct method) > 1
 23  order by person_id, acc;

 PERSON_ID        ACC       DIFF
---------- ---------- ----------
         1         10          1
         2         15          2
         2         19          6
         3         21          2

SQL>

Subtracting a column based on another column

2 Answers2