Create range to compare procedure overlap

[SuperSqlr]

I am using a simple database to try to figure out how to look at procedural overlaps.
I have 6 columns and one is a pk Proc_id. What I am trying to figure out is this:
Four of the columns are Start_time, Start_date, End_time, End_date. I have concatenated the Start_time and Start_date and cast them to a timestamp, same goes for end time and date but I need to create is a range so that I can see which unique procedures Proc_id overlap each other. I am pretty sure I have to Union the same table to solve this and have many ideas but I can't get my range set...First off I can't set the time stamp AS anything to make them comareable. Secondly if I don't convert them to timestamps I can't use overlap, right???

 

[TonyGroves]

How about something like:
[tt]
CREATE TEMPORARY TABLE t1 AS
SELECT
proc_id,
CAST(CONCAT(start_date,' ',start_time) AS DATETIME) sdt
CAST(CONCAT(end_date,' ',end_time AS DATETIME) edt
FROM t

CREATE TEMPORARY TABLE t2 AS
SELECT
proc_id,
CAST(CONCAT(start_date,' ',start_time) AS DATETIME) sdt
FROM t

SELECT *
FROM
t1 JOIN t2
ON
t1.proc_id<>t2.proc_id
AND t2.sdt BETWEEN s1.sdt AND s1.edt
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Correction!

I missed a bracket:[tt]
CAST(CONCAT(end_date,' ',end_time AS DATETIME) edt[/tt]
should be:[tt]
CAST(CONCAT(end_date,' ',end_time) AS DATETIME) edt[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[SuperSqlr]

So what I came up with is here, I still need to single out the MAX proc_id Overlap, like Select Max(....can't get it to work though, maybe a Order by Max Proc_id,...

SELECT a.proc_id, Count (a.proc_id), a.anest

FROM anest_procedures AS a,
anest_procedures AS b

WHERE (CAST (a.start_date || ' ' || a.start_time AS TimeStamp), CAST (a.end_date || ' ' || a.end_time AS TimeStamp))
OVERLAPS (CAST (b.start_date || ' ' || b.start_time AS TimeStamp), CAST (b.end_date || ' ' || b.end_time AS TimeStamp))
AND
b.anest = a.anest
GROUP BY a.proc_id, a.anest
ORDER BY a.proc_id;

 

[TonyGroves]

Sorry for being long-winded; my previous code, combined with yours, could be simplified as follows:
[tt]
SELECT
a.anest,
a.proc_id p1,
a.start_date sd1,
a.start_time st1,
b.proc_id p2,
b.start_date sd2,
b.start_time st2
FROM
anest_procedures a JOIN anest_procedures b
ON
a.anest=b.anest
AND a.proc_id<>b.proc_id
AND
CONCAT(b.start_date,b.start_time)
BETWEEN CONCAT(a.start_date,a.start_time)
AND CONCAT(a.end_date,a.end_time)
ORDER BY a.anest,a.proc_id
[/tt]
If a procedure starts between the start and end times of another procedure, then those procedures overlap, right? And that covers all overlap possibilities, I think. Simple string comparisons should work fine there.

One point about syntax: in MySQL the || operator means OR, not concatenation.


-----
ALTER world DROP injustice, ADD peace;