0

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.

1
  • Did you try through "sysdate" function. Commented Apr 12, 2018 at 6:03

2 Answers 2

1

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
0

Try this,

;

WITH CTE1
AS (
    SELECT id,NAME
        ,max(years) years
    FROM #tmp t
    GROUP BY id,NAME

    UNION ALL

    SELECT id,NAME
        ,max(years) - 1 years
    FROM #tmp t
    GROUP BY id,NAME
    )
SELECT t.id, t.NAME
    ,t.years
    ,isnull(t1.Sales, 0) Sales
FROM cte1 t
LEFT JOIN #tmp t1 ON t.NAME = t1.NAME
    AND t.years = t1.Years
ORDER BY t.NAME
    ,t.years DESC

CTE1 can also be recursive to make it last N years

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.