Skip to content
Advertisement

How to get Old Value from Incremental Year values?

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      |

Advertisement

Answer

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

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

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement