SQL Challenges-1: Consecutive Availability of a doctor in a clinic

SQL Challenges-1: Exercise-21 with Solution

A renowned doctor meets patient party in a famous clinic in the city.
From the following table write a SQL query to know the availability of the doctor for consecutive 2 or more days. Return visiting days.

Note:
The availability of doctor is boolean type. ('1' means available, and '0' means not available.).
Consecutive available seats are more than 2(inclusive) seats consecutively available.

Input:

Table: dr_clinic

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE dr_clinic (visiting_date date primary key, availability bool);

INSERT INTO dr_clinic VALUES ('2016-06-11','1');
INSERT INTO dr_clinic VALUES ('2016-06-12','1');
INSERT INTO dr_clinic VALUES ('2016-06-13','0');
INSERT INTO dr_clinic VALUES ('2016-06-14','1');
INSERT INTO dr_clinic VALUES ('2016-06-15','0');
INSERT INTO dr_clinic VALUES ('2016-06-16','0');
INSERT INTO dr_clinic VALUES ('2016-06-17','1');
INSERT INTO dr_clinic VALUES ('2016-06-18','1');
INSERT INTO dr_clinic VALUES ('2016-06-19','1');
INSERT INTO dr_clinic VALUES ('2016-06-20','1');	   
INSERT INTO dr_clinic VALUES ('2016-06-21','1');


SELECT DISTINCT a.visiting_date
FROM dr_clinic a JOIN dr_clinic b
  ON ABS(a.visiting_date - b.visiting_date) = 1
  AND a.availability = true AND b.availability = true
ORDER BY a.visiting_date;

Sample Output:

visiting_date|
-------------|
   2016-06-11|
   2016-06-12|
   2016-06-17|
   2016-06-18|
   2016-06-19|
   2016-06-20|
   2016-06-21|

Relational Algebra Expression:

Relational Algebra Tree:

OR

SQL Code(MySQL):

SELECT DISTINCT a.visiting_date FROM
    dr_clinic a JOIN dr_clinic b ON a.visiting_date = b.visiting_date + 1 
             OR a.visiting_date = b.visiting_date-1
    WHERE a.availability = 1 AND b.availability = 1
   ORDER BY a.visiting_date;

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Audience in the match.
Next: Sales Person.