mySQL - SELECT date range overlap in single table

Question

Sorry about possibly botching this question earlier...it still eludes me.

Basically, I have a single table that tracks a group of "approvers" for reports. also tracks when reports are "created" and when they were "submitted."

I am looking to flag when there are date/time overlaps for any reports.

Example (one table, "reports"):

"Created"                 "Submitted"                "ApproverID"
4/20/2012 5:01:32 AM          4/20/2012 5:30:32 AM          10
4/20/2012 5:08:32 AM          4/20/2012 5:45:32 AM          10
4/20/2012 5:01:32 AM          4/19/2012 5:38:16 PM          15

...my need is to return the following (where the same approver overlaps date/time)

 "Created"                 "Submitted"                "ApproverID"    
4/20/2012 5:01:32 AM          4/20/2012 5:30:32 AM          10            
4/20/2012 5:08:32 AM          4/20/2012 5:45:32 AM          10

so the approver (10 in this example) had a window that overlapped these reports. seems like a SELECT with a BETWEEN clause...but I am a bit stumped.

I had something simple like the following:

SELECT Created, LastModified, ApprovalGroupID, count(*) 
FROM   shifthandover.uniquereports
WHERE ApprovalGroupID between Created and LastModified

Help Appreciated!

If I understand correctly, it's definitely more complicated than a SELECT BETWEEN... — jadkik94
– jadkik94, Commented May 15, 2012 at 22:15

amaidment · Accepted Answer · 2012-05-15 22:16:05Z

3

From your question, I understand that an 'overlap' occurs when a second (or subsequent) report is created after a first (or earlier) report is created, but before that prior report is submitted - i.e. the approver was working on multiple reports at once. Is that correct?

You might try joining the reports table onto itself - e.g.:

SELECT * FROM reports r1 
JOIN reports r2 
ON r1.approvalId=r2.approvalId 
AND r2.created>r1.created 
AND r2.created<r1.submitted

answered May 15, 2012 at 22:16

amaidment

7,3866 gold badges58 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sanath · Accepted Answer · 2012-05-17 08:44:22Z

1

This provides the approver_id and the number of overlaps for that approver.

select approver_id, count(*)/2 from (
  select t1.approver_id 
  from reports t1 
  join reports t2 on 
     t2.approver_id = t1.approver_id 
     AND t1.id <> t2.id 
  where
     NOT((t1.submitted < t2.created) 
     OR (t1.created > t2.submitted))
  ) t2 group by approver_id;

The count(*)/2 is there so you don't double count the permutation where A overlaps B and B overlaps A. Also, I assume that you have a record_id or other primary key to prevent the record from counting itself.

edited May 17, 2012 at 8:44

Sanath

4,91211 gold badges58 silver badges88 bronze badges

answered May 16, 2012 at 14:12

user1351763

1264 bronze badges

Comments

nawfal · Accepted Answer · 2012-05-21 08:30:34Z

SELECT   Created, LastModified, ApprovalGroupID, count(*) --whatever those are
FROM     uniquereports AS r1
JOIN     uniquereports AS r2.ApproverID = r1.ApproverID AND r2 ON r2.id = r1.id -- the unique id of the table, or use whatever combination to get a unique representation, which is important.
WHERE    r2.Created >= r1.Created AND r2.Created <= r1.LastModified OR
         r2.LastModified >= r1.Created AND r2.LastModified <= r1.LastModified OR
         r2.Created <= r1.Created AND r2.LastModified >= r1.LastModified OR
         r2.Created >= r1.Created AND r2.LastModified <= r1.LastModified             
GROUP BY r1.ApproverID

The last OR clause is not useful here since we have already considered the case of r2.Created being greater than r1.Created in first OR clause, considering technically a record can not be modified before its created. Still added there to ensure all cases.

Collectives™ on Stack Overflow

mySQL - SELECT date range overlap in single table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related