MyVersion Explaining, MySQL

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);

#So lets first find all highest date for each employee that wasn't 40 hours.
Select max(endDate) AccStart, EmpID From SO46896406 Where Hours < 40 Group by EmpID;

#now that we have those, lets use the accStart for each employee to eliminate all employee records with a start date less than that 
#date.  Since we have to restart accruals when the hours fall below 40.

#So we join this to our base set as an "exclusion set" meaning we will get rid of all those records. while keeping all the other ones
SELECT A.*
     , 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 = B.EmpID
WHERE A.StartDate>=coalesce(B.AccStart,A.StartDate);

#Now we just need to sum the year differences and determine if the rate is 10 or 12.
Select EMPID, case when sum(yearsDiff) >= 5 and sum(yearsDiff) < 10 then 10
                   when sum(yearsDiff) >= 10 then 12 end as AccrualRate,
              sum(yearsDiff) as CurrentConsuctive40HrYears
FROM (
SELECT A.*
     , 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 = B.EmpID
WHERE A.StartDate>=coalesce(B.AccStart,A.StartDate))SUB
GROUP BY EmpID;

drop table if exists so46896406;


absolute service time: 0,38 sec