How to calculate and deduce the period of overlapping dates, by query in MySQL?
Best regards.
I need to obtain a list showing the work experience in months of several professionals, according to the data recorded in the corresponding table that is associated with the table of professionals.
Each professional may have 1 or more records (employment contracts) each with their start date and withdrawal date. But in some cases, they have had contracts whose periods overlap since they have worked in two entities at the same time but at different times.
The table tbl_profesionales has the fields with the general data, including, logically, a primary key id_profesional autoincremental type INT.
The fundamental fields of the table tbl_experiencia where the exp is recorded. are, among others:
id_experiencia (INT)
id_profesional (INT)
entidad_contrante (VARCHAR)
ref_contrato (VARCHAR)
cargo (VARCHAR)
rol_funciones (LONGTEXT)
fecha_inicio (DATETIME)
fecha_retiro (DATETIME)
Example:
The Professional A with id_profesional = 4319 has 5 different contracts with the following dates:
id_profesional Ref. Contratante Cargo Fecha Inicio Fecha Retiro
4319 C01 Empresa 1 Auditor 2010-03-15 2010-07-15
4319 C02 Empresa 2 Auditor 2010-05-10 2010-06-30
4319 C03 Empresa 1 Auditor 2010-08-10 2010-10-31
4319 C04 Empresa 1 Consultor 2010-11-01 2010-12-31
4319 C05 Empresa 3 Asesor 2010-12-15 2010-12-31
To validate experience in a position, it is required deduct the overlapping period in such cases (See contract C02).
How could I do this with a sql query going through the records associated with each professional, without having to pass input parameters to it?
Thank you very much.
2 answers
Test by joining both tables.
SELECT * FROM tbl_profesionales join tbl_experiencia on tbl_profesionales.id_profesional = tbl_experiencia.id_profesional WHERE ....
With this should cover your need
The problem is that each row depends on other rows in the same table, so to be able to do that computation, you would have, somehow, to have, in a single row, several.
The way to solve your problem would be to group all rows with overlapping ranges and keep the widest interval (minimum and maximum date of grouping), and add up there.
The query for overlapping ranges would be (tbl en tbl_experiencia, ini es fecha_inicio, fin is fecha_fin and id_profesional is w, from worker):
select a.w,
datediff(min(min(a.ini), min(b.ini)),
max(max(a.fin), max(b.fin))) as period
tbl as a INNER JOIN tbl as b on ((a.ini < b.fin and a.fin > b.ini)
or (a.ini = b.ini and a.fin = b.fin))
and a.w = b.w
group by ((a.ini < b.fin and a.fin > b.ini)
or (a.ini = b.ini and a.fin = b.fin))
and a.w = b.w
Seen by parties:
-
INNER JOIN: not to deal withNULLs. -
a.ini < b.fin and a.fin > b.inito join only with other rows that overlap with me, if they are from the same worker (a.w = b.w). - if a row does not overlap with any other of the same worker, it would disappear from the Union due to the
INNER JOIN, so we add, for each row oftbl, a union with itself:a.ini = b.ini and a.fin = b.fin. -
group byis exactly the same as Clauseon, so that it "removes the repetitions that we have created", so that we can apply summary functions on the created group (minandmaxfor each group).
With this, we have a row with the time interval between the minimum and maximum of each rank overlapped (or not), without ranks of different workers being mixed.
To obtain the work experience of each worker:
select agrup.w, sum(agrup.period) as experiencia
from (consulta_anterior) as agrup
I haven't tried it but it can be an approach to start readjusting up let it work.