CODEWITHSUNDEEP
sqlpostgresqlselectaggregate-functionspsql
uraza
uraza

Reputation: 957

Add additional column in a SELECT statement based on values of other columns PSQL

I have a table of driver licenses per person and know when a person acquired the drivers license. The validity of the drivers license can either be X days from the day you acquired it or a specific date. 

 acquired        relative      specific_date      valid_type     expiration_date
----------------------------------------------------------------------------------
2015-02-05         500             null              days
2015-02-05         null         2016-03-05           date
2015-02-05         200             null              days
2015-02-05         null         2016-02-22           date

My query right now would be:

SELECT acquired,
       relative_date,
       specific_date,
       valid_type
FROM person_drivers_license
WHERE (valid_type = 'days'
       AND (EXTRACT(epoch
                    FROM acquired) - EXTRACT(epoch
                                             FROM now()))/86400 + relative_date > 0)
  OR (valid_type = 'DATE'
      AND specific_date >= now()));

I am trying to add an expiration_date column with the select statement above. If it is a specific date, just take the date and put it in expiration_date and if it is a relative date, calculate the expiration date with the help of the acquired date. Is this possible in PSQL?

Upvotes: 0

Views: 163

Answers (3)

Andy K
Andy K

Reputation: 5044

You don't need pl/sql

SELECT acquired,
       relative_date,
       specific_date,
       valid_type,
       CASE WHEN specific date = "date" -- starts here
       THEN specific date 
       ELSE acquired + cast('1 months' as interval)END 
       AS expiration_date -- end here
FROM person_drivers_license
WHERE (valid_type = 'days'
       AND (EXTRACT(epoch
                    FROM acquired) - EXTRACT(epoch
                                             FROM now()))/86400 + relative_date > 0)
  OR (valid_type = 'DATE'
      AND specific_date >= now()));

Upvotes: 0

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28541

First - there is a simpler way to do date math in postgres. You can use something like:

acquired + relative_date * interval '1 day' >= current_date

or

acquired + relative_date >= current_date 
-- any integer can be treated as interval in days for date mathematics is SQL

For the question - try one of this:

CASE WHEN valid_type = 'days' 
     THEN acquired + relative_date * interval '1 day'
     WHEN valid_type = 'date'
     THEN specific_date
     --ELSE ??? you may specify something here
END

or

COALESCE(specific_date, acquired + relative_date * interval '1 day')

The query may look like:

SELECT acquired,
       relative_date,
       specific_date,
       valid_type,
       COALESCE(specific_date, acquired + relative_date * interval '1 day') as valid_date
FROM person_drivers_license
WHERE COALESCE(specific_date, acquired + relative_date * interval '1 day') >= current_date

Upvotes: 1

Robert Kock
Robert Kock

Reputation: 6018

Try this:

SELECT acquired,
       relative_date,
       specific_date,
       valid_type
       CASE valid_type
         WHEN 'days' THEN acquired + relative_date
         WHEN 'date' THEN specific_date
         ELSE NULL
       END AS expiration_date
FROM   person_drivers_license;

Upvotes: 1

Related Questions