1

I am struggling in finding a faster way to update the data I am currently working with. More precisely, I have workers' contracts data with starting and ending date for each contract. However, contracts' time-span may overlap, which means that a worker may start another work contract even if previous contract(s) are still active. Therefore, I want to isolate those contracts that are completely overlapped by some previous contract. In order to do so I made a stored procedure which compare the end of each contract with the end of the previous one. If the end of the current contract comes before the end of the previous one, I flag this contract which will be omitted in the next loop of the procedure until the end. However, since my database is composed by over 10 millions of observations, the stored procedure I made (written below) is excessively long. Therefore, I would like to create, if possible, just one query but I am struggling in finding a suitable solution. Any suggestion would be truly appreciated.

In the command below there are the replication of my database sample and the procedure I am currently using.

-- Table replication
drop table if exists my_table;
create table my_table (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    worker_id int,
    dt_start date,
    dt_end date,
    PRIMARY KEY (id)
);

insert into 
    my_table(id, worker_id, dt_start, dt_end)
values
    ('12', '20', '2014-05-02', '2014-07-08'),
    ('13', '20', '2017-01-14', '2017-01-31'),
    ('14', '20', '2017-04-18', '2018-01-01'),
    ('15', '20', '2017-11-06', '2017-11-06'),
    ('16', '20', '2017-11-06', '2017-12-07'),
    ('17', '20', '2019-12-02', '2020-05-31'),
    ('18', '20', '2020-06-01', '2020-07-31'),
    ('25', '29', '2014-11-24', '2017-02-11'),
    ('26', '42', '2016-01-22', '2016-05-05'),
    ('40', '71', '2016-12-01', '2017-05-31'),
    ('41', '71', '2017-06-01', '2020-12-21'),
    ('42', '71', '2020-07-17', '2020-08-02'),
    ('53', '380', '2017-02-15', '2017-07-31'),
    ('54', '380', '2017-09-04', '2017-12-23'),
    ('55', '380', '2017-12-27', '2018-12-22'),
    ('56', '380', '2019-05-15', '2019-09-15'),
    ('57', '380', '2020-03-23', '2099-01-01'),
    ('58', '380', '2020-09-28', '2022-09-30'),
    ('63', '391', '2013-07-23', '2013-11-30'),
    ('64', '391', '2014-06-16', '2014-12-16'),
    ('65', '391', '2014-11-21', '2015-01-20'),
    ('66', '391', '2015-04-01', '2015-04-15'),
    ('67', '391', '2015-06-10', '2015-06-22')
;
alter table my_table add index (id);
alter table my_table add index (worker_id);

-- Note: when the end date is '2099-01-01', it means the contract is an open-ended one and still ongoing

-- With this flag I will identify contracts completely overlapped, hence to discard
alter table my_table add column flag_del INT default 0;

-- Identify maximum number of contracts per person, I will use the maximum value for the loop
drop table if exists max_att;
create table max_att 
as select worker_id, count(*) n 
from my_table
group by worker_id;

-- Procedure to identify recursively contracts whose time-span is completely covedere by previous contracts
-- Those specific contracts will be idenfitied in the 'fla_del' column (= 1)
DROP PROCEDURE IF EXISTS doiterate;
delimiter //

CREATE PROCEDURE doiterate()
BEGIN
  DECLARE total INT unsigned DEFAULT 0;
  WHILE total <= (select MAX(n) from max_att) DO

with new_table as (
    select 
    *,
    lag(dt_end, 1) over (partition by worker_id order by id) dt_end_lag
    from my_table
    where flag_del = 0)
update my_table a
left outer join new_table b on a.id = b.id
set a.flag_del = 1 where b.dt_end_lag >= b.dt_end;

    SET total = total + 1;
  END WHILE;
END//  

delimiter ;

CALL doiterate(); 

select * from my_table;

Those entries flagged (at the end) with 1 will be removed because completely overlapped by a previous contract.

Desired output.

enter image description here

3
  • You should not trust id to see what contracts are "consecutive". If you could, then a self-join ON a.id = b.id-1 would let you pair up rows.
    – Rick James
    Sep 28, 2022 at 18:00
  • Show desired output for shown source data.
    – Akina
    Sep 28, 2022 at 18:47
  • Thank you so much @Akina for your response. It is quite close to what I have in my mind. I have slightly modified one of the entries in the example data, just to make it more realistic of cases I am facing in the original database. Furthermore, I have also added the desired output where the flagged rows are those I want to get rid of because they refer to contracts completely overlapped by a previous contract(s). Sep 28, 2022 at 20:48

1 Answer 1

0
WITH RECURSIVE
cte1 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY worker_id ORDER BY dt_start,dt_end) rn
  FROM test
),
cte2 AS (
  SELECT *, dt_start range_start, dt_end range_end
  FROM cte1
  WHERE rn = 1
  UNION ALL
  SELECT cte1.*, 
         CASE WHEN cte2.range_end <= cte1.dt_start
              THEN cte1.dt_start
              ELSE cte2.range_start
              END,
         GREATEST(cte2.range_end, cte1.dt_end)
  FROM cte2
  JOIN cte1 USING (worker_id)
  WHERE cte1.rn = cte2.rn + 1
)
SELECT id, worker_id, dt_start, dt_end,
       range_start,
       MAX(range_end) OVER (PARTITION BY worker_id, range_start) range_end
FROM cte2 
ORDER BY worker_id, dt_start, dt_end

https://dbfiddle.uk/7HjwGOrJ

Last 2 columns provides the date range which the current row date range is located in. Compose your final query using this technique.

PS. The query combines overlapped ranges (dt_end_1 > dt_start_2) and adjacent ranges (dt_end_1 = dt_start_2) but does not combine consecutive ones (dt_start_2 - dt_end_1 = 1 day). If you need to combine only overlapped ranges then use WHEN cte2.range_end < cte1.dt_start. If you need to combine consecutive ranges then use WHEN cte2.range_end + INTERVAL 1 DAY <= cte1.dt_start.

3
  • The 'problem' with the nice solution proposed by @Akina refers to worker_id number 391. More precisely with the contract with dt_start = 2014-11-21 and dt_end = 2014-12-16 because of the partial overlap with the subsequent contract. The query rightly assign the value 2015-01-20 to the field range_end but it should not be different its dt_end. Similarly, the range_start value for the subsequent row should not be 2014-06-16 but rather remains 2014-11-21. Sep 28, 2022 at 21:04
  • @NicolaCaravaggio ??? Rows id=64 and id=65 overlaps. Output provides minimal data range in which each of these rows are posessed completely. If you do not need in this for "previous" row then simply select the data from cte2 as-is. dbfiddle.uk/K6pD3RFy
    – Akina
    Sep 29, 2022 at 4:21
  • Thank you so much @Akina. I adjusted the code for the entire DB and it worked perfectly by selecting data from cte2, as you suggested, and also by performing the PARTITION BY for cte1 in descending order for the variable dt_end. Oct 1, 2022 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.