How to fetch last two year annual data in oracle

oracleoracle-11goracle-sql-developer

There is table named sales_data which I used for my project.
The table structure is below:

+------+------+------+--------+
|  ID  | Name | Year | Sales  |
+------+------+------+--------+
| 1000 | ABC  | 2016 |  50000 |
| 1000 | ABC  | 2017 |  80000 |
| 1000 | ABC  | 2015 |  90000 |
| 1000 | ABC  | 2014 |  45000 |
| 1000 | ABC  | 2013 |  30000 |
| 2000 | PQR  | 2017 |  80000 |
| 2000 | PQR  | 2015 |  90000 |
| 2000 | PQR  | 2014 |  75000 |
| 2000 | PQR  | 2013 |  60000 |
| 3000 | XYZ  | 2015 | 123000 |
| 3000 | XYZ  | 2013 |  56000 |
| 3000 | XYZ  | 2012 |  45000 |
| 3000 | XYZ  | 2011 |  30000 |
+------+------+------+--------+

From this table I want to fetch the latest consecutive annual data. My result set should be like below:

+------+------+------+--------+
|  ID  | Name | Year | Sales  |
+------+------+------+--------+
| 1000 | ABC  | 2017 |  80000 |
| 1000 | ABC  | 2016 |  50000 |
| 2000 | PQR  | 2017 |  80000 |
| 2000 | PQR  | 2016 |      0 |
| 3000 | XYZ  | 2015 | 123000 |
| 3000 | XYZ  | 2014 |      0 |
+------+------+------+--------+

Because for the PQR company the lastest data is 2017 so it is 80000. But because the 2016 data is not present in this table, the value needs to be 0.
Similar for XYZ company also.

Please help me on that. I am using Oracle 11g.

Best Answer

Sample data:

with data as
(
  select 1000 id, 'ABC' name, 2016 year, 50000 sales from dual union all
  select 1000 id, 'ABC' name, 2017 year, 80000 sales from dual union all
  select 1000 id, 'ABC' name, 2015 year, 90000 sales from dual union all
  select 1000 id, 'ABC' name, 2014 year, 45000 sales from dual union all
  select 1000 id, 'ABC' name, 2013 year, 30000 sales from dual union all
  select 2000 id, 'PQR' name, 2017 year, 80000 sales from dual union all
  select 2000 id, 'PQR' name, 2015 year, 90000 sales from dual union all
  select 2000 id, 'PQR' name, 2014 year, 75000 sales from dual union all
  select 2000 id, 'PQR' name, 2013 year, 60000 sales from dual union all
  select 3000 id, 'XYZ' name, 2015 year, 123000 sales from dual union all
  select 3000 id, 'XYZ' name, 2013 year, 56000 sales from dual union all
  select 3000 id, 'XYZ' name, 2012 year, 45000 sales from dual union all
  select 3000 id, 'XYZ' name, 2011 year, 30000 sales from dual
)

Result:

select 
  id, name,
  case when rn = 1 then year else fy - 1 end as year,
  case when (rn = 1 or fy - 1 = year) then sales else 0 end as sales
from
(
  select
    id, name, year, sales,
    rank() over (partition by name order by year desc) as rn,
    first_value(year) over (partition by name order by year desc) as fy
  from data
) 
where rn <= 2;

        ID NAM       YEAR      SALES
---------- --- ---------- ----------
      1000 ABC       2017      80000
      1000 ABC       2016      50000
      2000 PQR       2017      80000
      2000 PQR       2016          0
      3000 XYZ       2015     123000
      3000 XYZ       2014          0
Related Question