Styjun

I am trying to calculate the years of experience

Let's say someone has multiple employments as follow:

startdate enddate
2007-08-27 2008-05-09 
2007-08-27 2008-05-09 
2012-01-01 2018-07-31 
2013-01-06 2019-03-25

What would be the correct sql syntax to just select startdate, enddate which will be later pluged in a function to calculate the number of days for each employment?

Here's my expected result:

1st row: startdate 8/27/2007, enddate 5/9/2008 
2nd row: startdate 1/1/2012, enddate 3/25/2019 

The second employment took place during the same time for the 1st employment so, it will not be counted. The fourth employment started before the third employment ended so we should use the startdate for the third employment and enddate for the fourth employment

Use Distinct to remove dplicated records. Then you can use this query:

Select totaldays / 365 from 
(Select Sum(
 DATEDIFF(day, srartdate, enddate )
 ) As totaldays
 ) 

This query returns the dates without overlapping:

select 
 v.startdate startdate,
 min(vv.enddate) enddate
from view_appemployment v 
inner join view_appemployment vv 
on v.startdate <= vv.enddate
and not exists (
 select * from view_appemployment vvv 
 where vv.enddate >= vvv.startdate and vv.enddate < vvv.enddate
) 
where not exists (
 select * from view_appemployment vvvv 
 where v.startdate > vvvv.startdate and v.startdate <= vvvv.enddate
) 
group by v.startdate

See the demo

Results:

startdate | enddate 
 ------------------ | ------------------
27/08/2007 00:00:00 | 09/05/2008 00:00:00
01/01/2012 00:00:00 | 25/03/2019 00:00:00

Please try this:

SELECT concat(id," row: start date ",date_format(start_date,'%d/%m/%y')," end date ",date_format(end_date,'%d/%m/%y'))as dateinfo FROM `dates`

This is a Gaps & Islands in Sequences problem, the following query should do what you want:

CREATE TABLE #emp (empid int, startdate date,enddate date)
INSERT INTO #emp VALUES
(1,'2007-08-27','2008-05-09'),
(1,'2007-08-27','2008-05-09'),
(1,'2012-01-01','2018-07-31'),
(1,'2013-01-06','2019-03-25')

;WITH starts AS (
 SELECT em.*,
 (CASE WHEN EXISTS (SELECT 1
 FROM #emp em2
 WHERE em2.EmpID = em.EmpID AND
 em2.StartDate < em.StartDate AND
 em2.EndDate >= em.StartDate
 ) THEN 0 ELSE 1 END) AS [IsStart]
 FROM #emp em )

SELECT EmpID
 ,MIN(StartDate) AS [StartDate]
 ,MAX(EndDate) AS [EndDate]
FROM (SELECT s.*, SUM(isstart) OVER (PARTITION BY EmpID ORDER BY StartDate) AS [grp]
 FROM starts s
 ) s
GROUP BY EmpID, grp
ORDER BY EmpID