0

I've been trying to get total months and years of experiences based on data that I already have. Here is what I have tried so far

SELECT floor(m.ExperienceMonth/12) as yearsExp ,
       MOD(m.ExperienceMonth,12) as monthsExp 
from (SELECT SUM(TIMESTAMPDIFF(MONTH, e.datedebut,IF(e.datefin, e.datefin,current_timestamp())))AS ExperienceMonth 
      FROM experience e 
      WHERE  e.id_candidat=1) as m

But then I found a case where my script is not dealing with correctly. It's when the user has added multiple experiences in the same period of time. The same period will be added twice. This case is especially accurate for freelancers of part-time jobs.

Here's an example where the same user has added 2 experiences with crossed periods


INSERT INTO `experience`( `datedebut`, `datefin`,`id_candidat`, `label`) VALUES ('04/2018','10/2019',1,'experience 1'),('05/2019','12/2019',1,'experience 2')

If I keep the same script the period between 05/2019 and 10/2019 will be calculated twice so instead of getting 1 year and 8 months(difference between 04/2018 and 12/2019) I will get 2 years and 1 month (difference between 04/2018 and 10/2019)+(difference between 05/2019 and 12/2019)

fatim
  • 5
  • 3
  • 2
    Provide sample data (well-formatted CREATE TABLE + INSERT INTO), desired output for this data, detailed explanations. – Akina Jan 31 '22 at 10:09

0 Answers0