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:
- 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.
- 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
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.