MyVersion, MySQL - rextester

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),
(50,     30, '2016-01-01', '2016-10-01',    40),  
(51,     30, '2016-10-02',  '2017-01-01',    20),
(52,     30, '2017-01-01',  '2017-02-01',    40), 
(53,     30, '2017-02-02',  NULL       ,    40);

Select max(endDate) AccStart, EmpID From SO46896406 Where Hours < 40 Group by EmpID;

Select EMPID, case when sum(yearsDiff) >= 5 and sum(yearsDiff) < 10 then 10
                   when sum(yearsDiff) >= 10 then 12 end as AccrualRate
FROM (
SELECT A.*
--     , case when floor(datediff(coalesce(endDate,curDate()),StartDate)/365) >= 5 and floor(datediff(coalesce(endDate,curDate()),StartDate)/365) < 10 then 10
--            when floor(datediff(coalesce(endDate,curDate()),StartDate)/365) >= 10 then 12
--       else 0 end VacDays
     , datediff(coalesce(endDate,curDate()),StartDate)/365 as YearsDiff
FROM so46896406 A
LEFT JOIN (Select max(endDate) AccStart, EmpID From SO46896406 Where Hours < 40 Group by EmpID) B
  on A.EmpID = coalesce(B.EmpID, A.EmpID)
WHERE A.StartDate>=coalesce(B.AccStart,A.StartDate))SUB
GROUP BY EmpID

;

drop table if exists so46896406;


absolute service time: 0,34 sec