﻿ SQL Challenges-1: Consecutive Availability of a doctor in a clinic - 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 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.

﻿

## 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