CODEWITHSUNDEEP
sqlpostgresql
AdamMc331
AdamMc331

Reputation: 16730

Get Recent Quarters Without Dates

I'm tasked with pulling the data for the four recent quarters. If I was dealing with dates this would be easy, but I'm not sure how to do so when I have a quarters table that looks like this:

| quarter | year |
+---------+------+
|    1    | 2016 |
|    2    | 2016 |
|    3    | 2016 |
         ...

I know that I can get the current quarter by doing something like this:

SELECT * 
FROM quarters
WHERE quarter = (EXTRACT(QUARTER FROM CURRENT_DATE)) 
   AND year = (EXTRACT(YEAR FROM CURRENT_DATE));

However, I'm not sure the best way to get the four most recent quarters. I thought about getting this quarter from last year, and selecting everything since then, but I don't know how to do that with tuples like this. My expected results would be:

| quarter | year |
+---------+------+
|    1    | 2017 |
|    2    | 2017 |
|    3    | 2017 |
|    4    | 2017 |

Keep in mind they won't always be the same year - in Q12018 this will change.

I've built a SQLFiddle that can be used to tinker with this - http://sqlfiddle.com/#!17/0561a/1

Upvotes: 2

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Here is one method:

select quarter, year
from quarters
order by year desc, quarter desc
fetch first 4 rows only;

This assumes that the quarters table only has quarters with data in it (as your sample data suggests). If the table has future quarters as well, then you need to compare the values to the current date:

select quarter, year
from quarters
where year < extract(year from current_date) or
      (year = extract(year from current_date) and
       quarter <= extract(quarter from current_date)
      )
order by year desc, quarter desc
fetch first 4 rows only;

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

For the case that there can be gaps, like 2/2017 missing, and one would then want to return only three quarters instead of four, one can turn years and quarters into consecutive numbers by multiplying the year by four and adding the quarters.

select *
from quarters
where year * 4 + quarter 
      between extract(year from current_date) * 4 + extract(quarter from current_date) - 3
          and extract(year from current_date) * 4 + extract(quarter from current_date)
order by year desc, quarter desc;

Upvotes: 1

Related Questions