Search code examples
sqloracle-databasequery-performance

Oracledb query for the following usecase

I have an Availability table like below

AVAIL_DATE | AVAILABLE

AVAIL_DATE represents stay date and AVAILABLE is a char with 'T' or 'F'.

I need to check whether the availability table has true for all the dates within a given range (START_DATE, END_DATE)

I know I can achieve it like below queries

Operate to check whether it's all true 

    Select AVAILABLE from AVAILABILITY 
    where AVAIL_DATE BETWEEN (START_DATE, END_DATE);

I cannot figure out the Operate part and also I am not sure of the optimal oracledb query for this use case as I am a newbie to PL/SQL

Can someone help me with the query for this ?

Solution

  • Just count that the number of T values equals the number of dates:

    SELECT CASE
       WHEN COUNT( CASE available WHEN 'T' THEN 1 END ) = COUNT(*)
       THEN 'All days available'
       ELSE 'Not available'
       END AS is_available
FROM   AVAILABILITY 
WHERE  DATE BETWEEN START_DATE AND END_DATE;