CODEWITHSUNDEEP
postgresql
Kieran
Kieran

Reputation: 45

SQL check dates between columns and over rows

How can I write a query to tell me if specified date (e.g. 2015-01-15) falls within the range of ANY 'from_date' 'to_date' period in the table below? What's special here is that I only want one row per id returned.

id | from_date  | to_date
a  | 2015-01-01 | 2015-01-30
a  | 2015-04-01 | 2015-04-30

Current query:

select id,
case when from_date <= '2015-01-15'
     and to_date >= '2015-01-15'
     then 'true' else 'false' end as status
from dates

Current result:

id | status
a  | true
a  | false

Desired result:

id | status
a  | true

Upvotes: 0

Views: 59

Answers (2)

krithikaGopalakrishnan
krithikaGopalakrishnan

Reputation: 1335

I guess what you are looking for is DISTINCT ON. A DISTINCT ON on the id column outputs only one row per id.

Try the following query,

 select distinct on(id) id,
 case when from_date <= '2015-01-15'
 and to_date >= '2015-01-15'
 then 'true' else 'false' end as status from dates;

which outputs to,

   id | status 
   ----+--------
    a | true

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

select id,'true' as status
from dates
where '2015-01-15' between from_date and to_date;

Upvotes: 0

Related Questions