How to get Old Value from Incremental Year values?

Question

I have two tables:

Table1:

|  Year  |  CRN  |  CID   | Cap |
| 201910 |  14   |  ABC1  | 12  |
| 201910 |  15   |  ABC1  | 14  |
| 201820 |  25   |  ABC1  | 15  |
| 201820 |  26   |  ABC1  | 25  |
| 201810 |  43   |  ABC1  | 10  |
| 201720 |  55   |  ABC1  | 11  |

Table2:

|  Year   |  CRN  | BLCK  |
| 201910  |  14   |  A1   |
| 201910  |  15   |  A1   |
| 201820  |  25   |  B2   |
| 201820  |  26   |  B2   |
| 201810  |  43   |  C3   |
| 201720  |  55   |  C4   |
| 201720  |  95   |  F5   |
| 201710  |  65   |  D4   |

I want to return:

1. CID from Table1 for a CRN in Table2. Should return only one CID as one Year might have multiple CRNs for the same CID in Table1 and for the same BLCK in Table2. For example, for 201910, CRNs 14 and 15 have same CID ABC1 and same BLCK A1. So it should return ABC1 once.
2. Previous Year BLCK value and Sum of Cap values from Table1 for all the CRNs for CID found above(ABC1) in the previous Year. 201820 always comes after 201810 and the Year values are always incremented as 201810, 201820, 201910, 201920... For example, for 201910, I'll should get 40 as sum of Cap because there are two CRNs for CID ABC1 for Year 201820. Not sure if rownumber() will always work because I have to consider decreasing Year to go back from current value to previous value.

I am using Oracle 11g.

Expected Output:

| Year  |  CID |CurrYear Cap|CurrYear BLCK|Last Year|LastYear Cap|LastYear BLCK|
|201910 | ABC1 |      26    |      A1     | 201820  |    40      |     B2      |

Answer 1

You can use lag() function and grouping by year as

select "Year", "CID", "CurrYear Cap", "CurrYear BLCK",
       "Last Year", "LastYear Cap", "LastYear BLCK"
  from
    (
    select "Year", "CID", "CurrYear Cap", "CurrYear BLCK",
       lag("Year") over (order by "Year") as "Last Year", 
       lag("CurrYear Cap") over (order by "Year") "LastYear Cap",
       lag("CurrYear BLCK") over (order by "Year") "LastYear BLCK",
       row_number() over (order by "Year" desc) as rn
      from
        (
        with table1(Year, CRN, CID, Cap) as
        (
         select 201910 ,  14   ,  'ABC1'  , 12 from dual union all 
         select 201910 ,  15   ,  'ABC1'  , 14 from dual union all 
         select 201820 ,  25   ,  'ABC1'  , 15 from dual union all 
         select 201820 ,  26   ,  'ABC1'  , 25 from dual union all 
         select 201810 ,  43   ,  'ABC1'  , 10 from dual union all 
         select 201720 ,  55   ,  'ABC1'  , 11 from dual    
        )  ,
             table2(Year   ,  CRN  , BLCK) as
        (
         select 201910  ,  14   ,  'A1'   from dual union all
         select 201910  ,  15   ,  'A1'   from dual union all
         select 201820  ,  25   ,  'B2'   from dual union all
         select 201820  ,  26   ,  'B2'   from dual union all
         select 201810  ,  43   ,  'C3'   from dual union all
         select 201720  ,  55   ,  'C4'   from dual union all
         select 201720  ,  95   ,  'F5'   from dual union all
         select 201710  ,  65   ,  'D4'   from dual
        )
        select max(t1.year) as "Year", 
               max(t1.CID) as "CID", sum(t1.Cap) as "CurrYear Cap", max(t2.blck) as "CurrYear BLCK"           
          from table1 t1
          join table2 t2 on t1.year = t2.year and t1.crn = t2.crn 
         group by t1.year  
        ) 
    )
where rn = 1;

Year    CID   CurrYear Cap  CurrYear BLCK   Last Year   LastYear Cap  LastYear BLCK
------  ----  ------------  -------------   ----------  ------------  -------------- 
201910  ABC1       26             A1          201820         40             B2

if where rn = 1 at the last is replaced with order by rn, then all rows for all "Year" values are listed within an order.

Demo