Visualize SQL | Overlap Time and CROSS JOIN (part 4)

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

『part 4』 is almost the same as 『part 2』

The difference is ・・・・
『part 4』uses CROSS JOIN and see P1.time as overlap time.
『part 2』uses INNER JOIN and see P2.time as overlap time.

The same problem does not always have the same solution ・・・

Maybe Joe Celko intentionally chose 『part 4』

# how to see overlap time
[1] rep 1 : inside the orange 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

Just like 『part 2』, use only start time to check whether the time overlaps

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

#images of the time that overlaps
See P1 as the overlap time, Comparing to 『part 2』
the only difference is whether the overlap time is seen as P1 or P2 
the contents of below image is the same as 『part 2』

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/3b05143e-f89e-456b-a887-0259df8c21c0.png)

The P1 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/2db33acb-4bb7-a6f1-2ab4-f5f2b9fccd0b.png)

-----

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5c8c95dd-4206-c5e5-3346-2d854f0c8ccc.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 CORSS JOIN
『part 2』uses INNER JOIN. 『prar 4』uses CROSS JOIN.
both have the same combinations of time because they use 'anest_name' as condition

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7929124e-b861-967b-13d0-0796d0960e3c.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 CROSS JOIN

P1 and P2 are in opposite places, but the result is the same as 『part 2』

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/62ddb391-eb5e-73cd-ee86-7d8794a0f131.png)

----

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f2cd009b-6ec0-aae4-f10f-d10476048688.png)

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

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

![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/045a1c7f-e345-730d-d30c-88b9321f1764.png)

# Creating original data to be aggregated(2nd)
the order of combining data is reversed, but the result is the same.

Combining the overlap time to the Procs table using CROSS JOIN
to have all combinations data

Combining the Procs table to the Joined data using INNER JOIN in 『part 2』

```SQL:SQL
-- Combining the overlapped time to the Procs table using CROSS JOIN
-- to have all combinations data
SELECT P1.*,E1.*
FROM Procs AS P1,
(
	SELECT P2.anest_name, P2.start_time, COUNT(*) 
	FROM Procs AS P1, Procs AS P2 
	WHERE P1.anest_name = P2.anest_name 
		AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
	GROUP BY P2.anest_name, P2.start_time  -- different from 『part 2』,use P2
) AS E1(anest_name, etime, ecount)
WHERE E1.anest_name = P1.anest_name 
```
```SQL:SQL
-- change SQL a little to make it easier to see CROSS JOIN
WITH Pro AS (
	SELECT P2.anest_name AS anest_name, P2.start_time AS etime, COUNT(*) AS ecount
	FROM Procs AS P1, Procs AS P2 
	WHERE P1.anest_name = P2.anest_name
		AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
	GROUP BY P2.anest_name, P2.start_time  -- different from 『part 2』,use P2
)

SELECT P1.*,E1.*
FROM Procs AS P1,Pro AS E1  -- CROSS JOIN
WHERE E1.anest_name = P1.anest_name
```
```SQL:SQL
-- take the overlap time from all combinations
WITH Pro AS (
	SELECT P2.anest_name AS anest_name, P2.start_time AS etime, COUNT(*) AS ecount
	FROM Procs AS P1, Procs AS P2 
	WHERE P1.anest_name = P2.anest_name
		AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
	GROUP BY P2.anest_name, P2.start_time  -- different from 『part 2』, use P2
)

SELECT P1.*,E1.*
FROM Procs AS P1,Pro AS E1  -- CROSS JOIN
WHERE E1.anest_name = P1.anest_name
  AND E1.etime >= P1.start_time AND E1.etime < P1.end_time -- overlap conditions
```

![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/3a10e60f-9684-54b0-7cfb-c4cabea754b8.png)

-----

back to 『part 3』・・・ see below SQL

**Don't you see executing CROSS JOIN three times in a row?**
I hope you can imagine『part 3』SQL is able to transform to be like the form of 『part 2』

![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7c645338-91e1-e34b-2b20-81926579a19e.png)

#answer：Number of overlap time
the answer of #3 puzzle is below(max overlap time count for each proc_id)

```SQL:SQL
-- max overlap time count for each proc_id
WITH Pro AS (
    SELECT P2.anest_name AS anest_name, P2.start_time AS etime, COUNT(*) AS ecount
    FROM Procs AS P1, Procs AS P2 
    WHERE P1.anest_name = P2.anest_name
        AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
    GROUP BY P2.anest_name, P2.start_time  -- different from 『part 2』, use P2
)

SELECT P1.proc_id, P1.anest_name, MAX(E1.ecount) AS maxops
FROM Procs AS P1,Pro AS E1  -- CROSS JOIN
WHERE E1.anest_name = P1.anest_name
  AND E1.etime >= P1.start_time AND E1.etime < P1.end_time
  
GROUP BY P1.proc_id, P1.anest_name
```

```SQL:SQL
-- original answer SQL
SELECT P1.proc_id, P1.anest_name, MAX(E1.ecount) AS maxops
FROM Procs AS P1, 
(
	SELECT P2.anest_name, P2.start_time, COUNT(*)
	FROM Procs AS P1, Procs AS P2
	WHERE P1.anest_name = P2.anest_name 
		AND P1.start_time <= P2.start_time 
		AND P1.end_time > P2.start_time
	GROUP BY P2.anest_name, P2.start_time
) AS E1(anest_name, etime, ecount)
WHERE E1.anest_name = P1.anest_name 
	AND E1.etime >= P1.start_time AND E1.etime < P1.end_time

GROUP BY P1.proc_id, P1.anest_name
```

|proc_id |maxops|
|---|---|
|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)