I have a table of historical position tenures held by members within an organisation (tbl_tenue). Each position may only be held by one person at a time but one person may hold several positions sequentially or concurrently. I want to check the integrity of this table by taking each position tenure record in turn and then comparing it with every other record in the table looking for (erronous) overlaps with other tenures of the same position.
I have written a working query (below) that returns details of overlaps when passed a test_tenure_id, position_id, and the start and end dates of a particular tenure (see below) For each overlap. this query returns tenure_id, member_id, member_sn, date_started, date_ended and reason for the overlap.
Can anyone help me with the sql to now run this query against the same tbl_tenue table, each time passing it the data from one row in tbl_tenue so I can test each record for overlap with every other one (except itself of course) and return data from the record and all its overlaps?
(I realise that if I can do this then I should be able to avoid passing in the tenure_id to the WHERE clause by using the join and also avoid passing the dates as well using the join but I can't see how to do that at the moment, so any help with that would be good)
The query below uses the following tables, simplified for this question
TABLE tbl_member
( member_id INT AUTO_INCREMENT, -- pk
member_sn` varchar(50) , --surname
<other stuff>
)
TABLE tbl_tenure
(tenure_id INT AUTO_INCREMENT, -- pk
member_id INT -- fk to tbl_member
position_id -- fk to table of position titles
date_started DATE
date_ended DATE -- will be NULL if still in post
)
-- test data for query
SET @the_test_tenure_start_date = '2016-05-13' ;
SET @the_test_tenure_end_date = '2016-10-05';
SET @the_test_position_id = 18;
SET @the_test_tenue_id = 122;
-- the query to return overlaps with data from a given tenure record
SELECT
tbl_tenure.tenure_id,
tbl_tenure.member_id,
tbl_member.member_sn,
tbl_tenure.date_started,
tbl_tenure.date_ended,
CASE
WHEN @the_test_tenure_end_date <= IFNULL(date_ended, CURDATE()) -- test end date <= existing end date
AND @the_test_tenure_start_date >= date_started -- test start date >= existing start date
THEN 'Test dates fall completely inside an existing tenure'
WHEN @the_test_tenure_end_date >= IFNULL(date_ended, CURDATE()) -- test end date >= existing end date
AND @the_test_tenure_start_date <= date_started -- test start date <= existing start date
THEN 'An existing tenure falls completely inside test dates'
WHEN @the_test_tenure_start_date >= date_started -- test start date >= existing start date
AND @the_test_tenure_start_date <= IFNULL(date_ended, CURDATE()) -- test start date <= existing end date
THEN 'Test start date overlaps with an existing tenure'
WHEN @the_test_tenure_end_date >= date_started -- test end date >= existing start date
AND @the_test_tenure_end_date <= IFNULL(date_ended, CURDATE()) -- test end date <= existing end date
THEN 'Test end date overlaps with an existing tenure'
END AS reason
FROM
tbl_tenure
INNER JOIN tbl_member
ON tbl_tenure.member_id = tbl_member.member_id
WHERE ( -- there is an overlap (see qry 2.2 http://salman-w.blogspot.co.uk/2012/06/sql-query-overlapping-date-ranges.html
@the_test_tenure_end_date >= date_started)
AND
IFNULL(date_ended, CURDATE()) >= @the_test_tenure_start_date
)
AND tbl_tenure.position_id = @the_test_position_id -- position to be tested
AND tbl_tenure.tenure_id <> @the_test_tenue_id -- don't look at the test tenure record
ORDER BY tbl_tenure.date_started ASC;
In order to clarify this question, the output I am looking for is something like this, note tenure_id 132 where a member is recorded as ovelapping with themself
tenure_id | member_id | position_id | start_date | end_date | overlapping_member_id | overlapping_tenure_id | overlapping_start_date |overlapping_end_date | overlap_reason
123 | 2 | 6 | 2016-02-01 | 2016 02-01 | 7 | 456 | 2016-01-05 | 2016-01-10 |'Test start date overlaps with an existing tenure'
125 | 2 | 8 | 2016-02-01 | 2016 03-01 | 8 | 459 | 2016-01-0 | 2016-02-01 |'Test end date overlaps with an existing tenure'
129 | 4 | 7 | 2016-03-10 | 2016 04-01 | 6 | 501 | 2016-03-2 | 2016-03-25 |'An existing tenure falls completely inside test dates'
132 | 4 | 7 | 2016-01-01 | 2016 04-01 | 4 | 505 | 2016-03-01 | 2016-04-01 |'Test end date overlaps with an existing tenure'
135 | 9 | 3 | 2016-05-01 | 2016 07-01 | 9 | 520 | 2016-04-0 | 2016-08-01 |'Test dates fall completely inside an existing tenure'
