Search code examples
sqlpostgresqlansi-sql

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.

Solution

  • 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
)