CODEWITHSUNDEEP
databaseoracle-databaseoracle11goracle12c
SwapnaSubham Das
SwapnaSubham Das

Reputation: 537

How to fetch last two quarter data from Oracle table

I have one table named "Quarter_revenue". Sample of the table structure is below

Year    Quarter Revenue
2017    Q1  10000
2017    Q1  20000
2017    Q2  30000
2017    Q2  40000
2017    Q3  50000
2017    Q3  60000
2017    Q4  70000
2017    Q4  80000
2016    Q1  90000
2016    Q1  100000
2016    Q2  110000
2016    Q2  120000
2016    Q3  130000
2016    Q3  140000
2016    Q4  150000
2016    Q4  160000
2015    Q1  170000
2015    Q1  180000
2015    Q2  190000
2015    Q2  200000
2015    Q3  210000
2015    Q3  220000
2015    Q4  230000
2015    Q4  240000

There is no date column in this table. I want to fetch last two quarter data from this table. This table will update in every quarter. Please help me on that..

Upvotes: 2

Views: 1866

Answers (2)

user5683823
user5683823

Reputation:

Assuming "last two quarters" means current calendar quarter and previous one, you could try

select <whatever>
from   your_table
where  yr || qtr = to_char(sysdate, 'yyyy"Q"q')
   or  yr || qtr = to_char(add_months(sysdate, -3), 'yyyy"Q"q')

I assume your columns are not really called YEAR and QUARTER - I used YR and QTR for this illustration.

If you mean in March 2018 you must select quarters 3 and 4 of 2017, you can modify the WHERE clause to add_months(sysdate, -3) and add_months(sysdate, -6).

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

If you want the last two quarters from the entire table, then dense rank should work:

SELECT Year, Quarter, Revenue
FROM
(
    SELECT Year, Quarter, Revenue,
        DENSE_RANK() OVER (ORDER BY Year DESC, Quarter DESC) dr
    FROM yourTable
) t
WHERE dr IN (1, 2)
ORDER BY
    Year, Quarter

enter image description here

Demo

The demo is in SQL Server, but it should completely work in Oracle as well. Setting up Oracle demos is also a pain.

Upvotes: 1

Related Questions