How to fetch last two year annual data in oracle

Question

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 latest consecutive annual data. My result set is need to 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 PQR company lastest data is 2017 so it is 80000 but 2016 data is not present in this table so it's need to be 0. Similar for XYZ company also.

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

Answer 1

You can try this.

SELECT  t2.ID,t2.Name,t2.years,COALESCE(t1.Sales,0) Sales
FROM sales_data t1
RIGHT JOIN
(
  SELECT ID,Name,MAX(Year)   years
  FROM sales_data
  GROUP BY ID,Name
  UNION ALL 
  SELECT ID,Name,MAX(Year) - 1  years
  FROM sales_data
  GROUP BY ID,Name
) t2 ON t1.ID = t2.ID AND t1.Name = t2.Name AND  t1.Year = t2.years 
ORDER BY t2.ID,t2.years DESC

sqlfiddle: http://sqlfiddle.com/#!4/4fc37c/5

Answer 2

WITH Years AS
(
  SELECT ID,Name,MAX(Year) AS year
  FROM Table1
  GROUP BY ID,Name
  UNION ALL
  SELECT ID,Name,MAX(Year) - 1 AS year
  FROM Table1
  GROUP BY ID,Name
)
SELECT  y.ID,
        y.Name,
        y.year,
        COALESCE(t.Sales,0) AS Sales
FROM Years y
LEFT JOIN Table1 t
ON t.ID = y.ID 
AND t.Name = y.Name 
AND t.Year = y.year 
ORDER BY y.ID,y.year DESC 

Demo

http://sqlfiddle.com/#!4/c20fd/21

Answer 3

Yet another option:

SQL> with test (id, year, sales) as
  2    (select 1000, 2016, 50000 from dual union
  3     select 1000, 2017, 80000 from dual union
  4     select 1000, 2015, 90000 from dual union
  5     select 1000, 2014, 45000 from dual union
  6     --
  7     select 2000, 2017, 80000 from dual union
  8     select 2000, 2015, 90000 from dual union
  9     select 2000, 2014, 75000 from dual union
 10     --
 11     select 3000, 2015, 123000 from dual union
 12     select 3000, 2013, 56000 from dual union
 13     select 3000, 2012, 45000 from dual
 14    ),
 15  years as
 16    (select id, min_year + column_value - 1 year
 17     from (select id, min(year) min_year, max(year) max_year
 18           from test
 19           group by id
 20          ),
 21          table(cast(multiset(select level from dual
 22                              connect by level <= max_year - min_year + 1
 23                             ) as sys.odcinumberlist))
 24    ),
 25  ranking as
 26    (select y.id, y.year, nvl(t.sales, 0) sales,
 27       row_number() over (partition by y.id order by y.year desc) rn
 28     from years y left join test t on t.id = y.id and t.year = y.year
 29    )
 30  select r.id, r.year, r.sales
 31  from ranking r
 32  where r.rn <= 2
 33  order by r.id, r.year desc;

        ID       YEAR      SALES
---------- ---------- ----------
      1000       2017      80000
      1000       2016      50000
      2000       2017      80000
      2000       2016          0
      3000       2015     123000
      3000       2014          0

6 rows selected.

SQL>

Answer 4

Try this

SELECT sd1.ID,  sd1.name, sd2.year, COALESCE(sd3.sales,0) 
FROM
(SELECT distinct ID, name from sales_data) as sd1 join 
(SELECT distinct year from sales_data ) as sd2
Left join sales_data sd3
on sd1.ID = sd3.ID AND sd2.year = sd3.year