Visualize SQL | Overlap Time and INNER JOIN (part 2)

# Beginning
This technique is based on SQL Puzzle # 3 anesthesia

In this puzzle, it is required to get overlap time from data that contains time fields.
How many jobs are overlapped for each job(proc_id)?

The condition of overlap time is different from #3 『part 1』
The final overlap time will be the same

# how to see overlap time
[1] rep 1 : inside the blue dotted lines are the time that overlaps
[2] rep 2 : check the start time of the time that overlaps with the start time
[3] rep 3 : check the end time of the time that overlaps with the start time

what is the big difference from the (part 1)'s overlapping conditions is **to check star time only**

Create all combinations and hold the start time on the P1 side.
Hold the start time and end time of all combinations on the P2 side.
Judging whether P1.star_time between P2.star_time and P2.end_time?

#images of the time that overlaps
See P2 as the overlap time

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/31f7a0df-6390-5bb1-62b8-d0489b5fa18d.png)

The P2 time that fits the conditions of both (rep 2) and (rep 1) is the overlap time


![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/112ab3de-d6a0-a09b-0c17-7e56485c6c9d.png)

-----

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7bc06594-441e-e449-1d85-7a0c52001543.png)

#Proc table 
```SQL:SQL
CREATE TABLE Procs(
	proc_id integer,
	anest_name text,
	start_time text,
end_time text);

INSERT INTO Procs VALUES(10,'Baker','08:00','11:00');
INSERT INTO Procs VALUES(20,'Baker','09:00','13:00');
INSERT INTO Procs VALUES(30,'Dow','09:00','15:30');
INSERT INTO Procs VALUES(40,'Dow','08:00','13:30');
INSERT INTO Procs VALUES(50,'Dow','10:00','11:30');
INSERT INTO Procs VALUES(60,'Dow','12:30','13:30');
INSERT INTO Procs VALUES(70,'Dow','13:30','14:30');
INSERT INTO Procs VALUES(80,'Dow','18:00','19:30');
```

# Join data using INNER JOIN
『part 1』use CROSS JOIN, 『part 2』use INNER JOIN. Both have the same output result

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/29275540-b76c-b50c-7b8d-47d967fe24e4.png)

# Creating original data to be aggregated(1st)
Dr.Baker has two jobs(proc_id) 10 and 20. All combinations are 2 x 2 = 4
Dr.Dow has six jobs(proc_id) 30,40,50,60,70 and 80. All combinations are 6 x 6 = 36.

Let's make all combinations of data using INNER JOIN

Different from『part 1』,there is a slight difference in the output
because the overlap conditions are different.
However the final answer will be the same.


![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/356c4d65-2137-e7d2-f0d4-4cc2244294a1.png)


-----

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c36bc5b1-7c10-603a-b0bf-1d7f36dbef7c.png)

# Overlap Time
With the results above, the overlap time is taken. Count how much P1.start_time overlap with P2.time

```SQL:SQL
-- number of count that overlaps with P1.start_time
SELECT P1.anest_name, P1.start_time, COUNT(*)
FROM Procs AS P1 INNER JOIN Procs AS P2
ON P1.anest_name = P2.anest_name
	AND P2.start_time <= P1.start_time AND P2.end_time > P1.start_time
GROUP BY P1.anest_name, P1.start_time -- doctor, starting time
```

![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/bbf3aff4-3d5d-cce1-c00d-0110b79c11d8.png)

# Creating original data to be aggregated(2nd)
Combine the original table 『Procs』to the data which was taken above,
　⇒ to create all combinations of data ・・・again
then get the time that P1.start_time overlaps

```SQL:SQL
--  all combinations of P2(start and end) for P1.start
SELECT Con.*,P3.*										
FROM                                                    
(                                                       
   SELECT P1.anest_name, P1.start_time, COUNT(*) 
   FROM Procs AS P1 INNER JOIN Procs AS P2
      ON P1.anest_name = P2.anest_name
      AND P2.start_time <= P1.start_time AND P2.end_time > P1.start_time                     
   GROUP BY P1.anest_name, P1.start_time               
) AS Con(anest_name, start_time, tally)                 

INNER JOIN Procs AS P3 ON Con.anest_name = P3.anest_name
```

```SQL:SQL
-- overlap time
SELECT Con.*,P3.*
FROM
(
	SELECT P1.anest_name, P1.start_time, COUNT(*)
	FROM Procs AS P1 INNER JOIN Procs AS P2
	  ON P1.anest_name = P2.anest_name
	  AND P2.start_time <= P1.start_time AND P2.end_time > P1.start_time
	GROUP BY P1.anest_name, P1.start_time
) AS Con(anest_name, start_time, tally)

INNER JOIN Procs AS P3 ON Con.anest_name = P3.anest_name
   AND P3.start_time <= Con.start_time AND P3.end_time > Con.start_time -- overlapped conditions
```

![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5bd36c0f-fb2e-1b85-d8ca-d9dae54db9f0.png)

# why create all combinations of data twice?
this puzzle is required the max count of overlap time for each proc_id.
So that you should consider of the time below image.

![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d055053a-fd70-4405-231e-7e6711d11b6b.png)

#answer：Number of overlap time
the answer of #3 puzzle is below(max overlap time count for each proc_id)
**It is summarized by P3.proc_id that is joined at end**

```SQL:SQL
-- max overlap time count for each proc_id
SELECT P3.proc_id, MAX(Con.tally)
FROM
(
	SELECT P1.anest_name, P1.start_time, COUNT(*) AS tally
	FROM Procs AS P1 INNER JOIN Procs AS P2
	  ON P1.anest_name = P2.anest_name
	  AND P2.start_time <= P1.start_time AND P2.end_time > P1.start_time
	GROUP BY P1.anest_name, P1.start_time
) AS Con(anest_name, start_time, tally)

INNER JOIN Procs AS P3 ON Con.anest_name = P3.anest_name
	AND P3.start_time <= Con.start_time AND P3.end_time > Con.start_time
GROUP BY P3.proc_id -- this is the point, in the blue square frame
```

|proc_id |max |
|---|---|
|10|2|
|20|2|
|30|3|
|40|3|
|50|3|
|60|3|
|70|2|
|80|1|

# References
[Joe Celko's SQL Puzzles and Answers]
(https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)