w3resource

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:

FieldTypeNullKeyDefaultExtra
visiting_datedateNOPRI
availabilitytinyint(1)YES

Data:

visiting_dateavailability
2016-06-111
2016-06-121
2016-06-130
2016-06-141
2016-06-150
2016-06-160
2016-06-171
2016-06-181
2016-06-191
2016-06-201
2016-06-211

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 Expression: Consecutive Availability of a doctor in a clinic.

Relational Algebra Tree:

Relational Algebra Tree: Consecutive Availability of a doctor in a clinic.

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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8