Currently I am working on staff management system that allows to setup subordinate relations between two positions (e.g. Software Engineer is a subordinate of Team Lead). In database schema it looks like:
position_managers (position_id, manager_id, start_date, end_date)
-- The start_date and end_date are the date range during which the relationship is valid
team_lead, ceo, 2021-01-01, 9999-12-31
software_engineer, team_lead, 2021-01-01, 2021-12-31
But currently I need to implement the second data source (that comes from foreign system) which will override subordinate relations:
position_overridden_managers (position_id, manager_id, start_date, end_date)
software_engineer, ceo, 2021-01-01, 2021-06-20
software_engineer, hr, 2021-06-25, 2021-08-20
I want to select all rows from position_managers and position_overridden_managers and combine them into one result set, respecting the overridden rules.
position_id, manager_id, start_date, end_date
team_lead, ceo, 2021-01-01, 9999-12-31
software_engineer, ceo, 2021-01-01, 2021-06-20
software_engineer, team_lead, 2021-06-21, 2021-06-24
software_engineer, hr, 2021-06-25, 2021-08-20
software_engineer, team_lead, 2021-08-21, 2021-12-31
UPD: I found this blog post - https://blog.crunchydata.com/blog/range-types-recursion-how-to-search-availability-with-postgresql
But the SQL in the blog post was a bit buggy and I found a fix for this blog post - https://stackoverflow.com/a/62142229/2585154
I adapted this solution for my purpose: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a598b9cc892746a0a6c20b1deb473357
Is this the best way to achieve the desired result?
UPD2: Found solution is also buggy. https://dbfiddle.uk/?rdbms=postgres_12&fiddle=3aad6bb0fabe1ffd580ec03126c92848
It makes duplicate row on following data-set:
-- managers
(1, 'first', '2017-09-01'::date, '2018-10-14'::date),
(1, 'second', '2018-10-15'::date, '2019-10-31'::date),
(1, 'third', '2019-11-01'::date, '9999-12-31'::date)
-- override
(1, 'override', '2021-01-01'::date, '9999-12-31'::date)
Output is:
position_id manager start_date end_date
1 first 2017-09-01 2018-10-14
1 second 2018-10-15 2019-10-31
1 third 2019-11-01 2020-12-31
-- incorrect duplicate below
1 third 2019-11-01 9999-12-31
1 override 2021-01-01 9999-12-31
UPD3: Found another solution https://stackoverflow.com/a/23165263/2585154 and adapted for myself. It works OK on different data sets:
