SO 2, MySQL - rextester

SO 2

Language: Layout:

drop table if exists SO46896406;

Create table SO46896406 (
  ID int,  
  EmpID int, 
  startdate date, 
  enddate date,
  hours int);
  
Insert into SO46896406 (ID, EmpID, StartDate, EndDate, Hours) values 
(1 ,     1 ,'2011-06-01', NULL       ,    40),
(2 ,     2 ,'2011-10-10', NULL       ,    40),
(3 ,     3 ,'2000-03-01', NULL       ,    40),
(5 ,     5 ,'1999-06-07', NULL       ,    40),
(6 ,     6 ,'2012-05-16', NULL       ,    40),
(7 ,     7 ,'1996-09-25', NULL       ,    40),
(8 ,     8 ,'2015-10-07', NULL       ,    40),
(9 ,     8 ,'2012-10-17','2015-10-06',    40),
(12 ,     9 ,'2000-06-05', NULL       ,    40),
(16 ,    12 ,'2005-08-04', NULL       ,    40),
(17 ,    13 ,'2008-01-20', NULL       ,    40),
(19 ,    14 ,'1999-02-17', '2001-05-31',    40),
(22 ,    16 ,'2016-04-01', NULL       ,    40),
(23 ,    16 ,'1999-11-01', '2016-03-31' ,    40),
(29 ,    18 ,'1997-08-01', NULL       ,    40),
(31 ,    19 ,'2012-02-13', NULL       ,    40),
(36 ,    22 ,'2006-04-05', NULL       ,    40),
(38 ,    23 ,'2015-07-01', '2016-03-12',    40),
(44 ,    27 ,'2016-03-07', '2017-03-03',    40),
(46 ,    28 ,'2002-11-11', NULL       ,    40),
(47 ,    29 ,'2016-04-11', NULL       ,    40);

Select * from SO46896406;

select EmpID, 
case when enddate is null then (DAY(now()) - Day(startdate))/365
     else (YEAR(enddate) - YEAR(startdate))/365
end as diff_years
from so46896406
where hours=40
group by EmpId
having max(startdate);

drop table if exists so46896406;


absolute service time: 0,38 sec

	ID	EmpID	startdate	enddate	hours
1	1	1	01.06.2011 00:00:00	NULL	40
2	2	2	10.10.2011 00:00:00	NULL	40
3	3	3	01.03.2000 00:00:00	NULL	40
4	5	5	07.06.1999 00:00:00	NULL	40
5	6	6	16.05.2012 00:00:00	NULL	40
6	7	7	25.09.1996 00:00:00	NULL	40
7	8	8	07.10.2015 00:00:00	NULL	40
8	9	8	17.10.2012 00:00:00	06.10.2015 00:00:00	40
9	12	9	05.06.2000 00:00:00	NULL	40
10	16	12	04.08.2005 00:00:00	NULL	40
11	17	13	20.01.2008 00:00:00	NULL	40
12	19	14	17.02.1999 00:00:00	31.05.2001 00:00:00	40
13	22	16	01.04.2016 00:00:00	NULL	40
14	23	16	01.11.1999 00:00:00	31.03.2016 00:00:00	40
15	29	18	01.08.1997 00:00:00	NULL	40
16	31	19	13.02.2012 00:00:00	NULL	40
17	36	22	05.04.2006 00:00:00	NULL	40
18	38	23	01.07.2015 00:00:00	12.03.2016 00:00:00	40
19	44	27	07.03.2016 00:00:00	03.03.2017 00:00:00	40
20	46	28	11.11.2002 00:00:00	NULL	40
21	47	29	11.04.2016 00:00:00	NULL	40

	EmpID	diff_years
1	1	0,0603
2	2	0,0356
3	3	0,0603
4	5	0,0438
5	6	0,0192
6	7	-0,0055
7	8	0,0438
8	9	0,0493
9	12	0,0521
10	13	0,0082
11	14	0,0055
12	16	0,0603
13	18	0,0603
14	19	0,0274
15	22	0,0493
16	23	0,0027
17	27	0,0027
18	28	0,0329
19	29	0,0329