CODEWITHSUNDEEP
sqlpostgresqlansi-sql
FabianoLothor
FabianoLothor

Reputation: 2977

Better way to write a SQL conditional [EDITED - Featured Needed]

I have the code below in my query, but I don't like it.

     (
       (year + 1) = date_part('YEAR', CURRENT_DATE) OR
       year = date_part('YEAR', CURRENT_DATE) OR
       (year - 1) = date_part('YEAR', CURRENT_DATE)
     )

Exists a better form to write this conditional? The objective of this conditional is returns the values between the post year and the before year of today.

EDIT

I want a new feature:

  SELECT DISTINCT ON (alias.year) alias.year, alias.another_column
    FROM scheme.table AS alias
   WHERE alias.active IS TRUE AND ABS(alias.year- date_part('YEAR', CURRENT_DATE)) IN (0,1)
   ORDER BY alias.year, alias.another_column DESC;

The code above returns:

enter image description here

I want:

2017 - 8
2018 - 1
2019 - 1

This occurs because no exists any record with year 2019, but when not exists some year, I need return him with the value 1 in another column.

Upvotes: 0

Views: 71

Answers (3)

Jpsh
Jpsh

Reputation: 1726

Try this, the between will match values in between the low and high value.

(date_part('YEAR', CURRENT_DATE) BETWEEN (year - 1) AND (year + 1))

EDIT:

To accomplish what you're talking about it may be easier to use a common table expression and a RIGHT OUTER JOIN the common table expression will fill with the years in the range (last year, this year, next year) and will limit records to what is in the cte, even if records do not exist in the table for that year.

WITH RECURSIVE cte(year) AS(
    VALUES (date_part('YEAR', CURRENT_DATE)-1)
    UNION ALL 
    SELECT year+1 FROM cte WHERE year<date_part('YEAR', CURRENT_DATE)+1
)

SELECT DISTINCT ON (cte.year) alias.year, COALESCE(alias.another_column, 'value when blank') another_column
FROM scheme.table AS alias
RIGHT OUTER JOIN cte
    ON alias.year = cte.year
WHERE alias.active IS TRUE 
ORDER BY cte.year, alias.another_column DESC;

so records would show like this

2017 - 8
2018 - 1
2019 - value when blank

If you remove the COALESCE function it would look like this

2017 - 8
2018 - 1
2019 - NULL

EDIT:

As suggesting in comments you can also use generate_series() to create the common table expression. since date_part returns a double precision so you will have to CAST it to a integer I've used two methods so you have more options

WITH cte AS (
    SELECT 
      generate_series(
        date_part('YEAR', CURRENT_DATE)::integer-1
        ,CAST(date_part('YEAR', CURRENT_DATE) AS INTEGER)+1
      ) AS year
)

Upvotes: 2

Dessma
Dessma

Reputation: 609

Not familiar with the specifics of this DBMS but that should work :

year BETWEEN (date_part('YEAR', CURRENT_DATE)-1) AND (date_part('YEAR', CURRENT_DATE)+1)

It also reads just like your requirement, which is usually a good thing for the person who will maintain your code in the future.

Upvotes: 1

MRsa
MRsa

Reputation: 684

ABS(year - date_part('YEAR', CURRENT_DATE)) IN (0,1)

Upvotes: 1

Related Questions