MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `EmployeeRole` (
  `employeeId` int ,
  `effectiveFrom` datetime ,
  `grade` varchar(30)
) ;

insert into EmployeeRole (employeeId, `effectiveFrom`,`grade`) 
  values 
    (1,'2022-01-01','A'),
    (1,'2022-01-15','B'),
    (2,'2022-01-01','A'),
    (3,'2022-01-01','A'),
    (3,'2022-01-15','B'),
    (3,'2022-01-25','A');


Records: 6  Duplicates: 0  Warnings: 0


SELECT
   employeeID,
   grade,
   effectiveFrom,
   DATEDIFF(COALESCE(LEAD(effectiveFrom) 
                             OVER (PARTITION BY employeeID ORDER By effectiveFrom),
                     DATE_ADD(LAST_DAY(effectiveFrom),INTERVAL 1 DAY)),
            effectiveFrom) as '#Days'
FROM EmployeeRole;


employeeID
grade
effectiveFrom
#Days


1
A
2022-01-01 00:00:00
14

1
B
2022-01-15 00:00:00
17

2
A
2022-01-01 00:00:00
31

3
A
2022-01-01 00:00:00
14

3
B
2022-01-15 00:00:00
10

3
A
2022-01-25 00:00:00
7


SELECT
   employeeID,
   grade,
   SUM(`#Days`)
FROM (

SELECT
   employeeID,
   grade,
   effectiveFrom,
   DATEDIFF(COALESCE(LEAD(effectiveFrom) 
                            OVER (PARTITION BY employeeID ORDER By effectiveFrom),
                     DATE_ADD(LAST_DAY(effectiveFrom),INTERVAL 1 DAY)),
            effectiveFrom) as '#Days'
FROM EmployeeRole
) x
GROUP BY
  employeeID,
   grade;


employeeID
grade
SUM(`#Days`)


1
A
14

1
B
17

2
A
31

3
A
21

3
B
10


SELECT 
    CURDATE() as "CurrentDate",
    LAST_DAY(CURDATE()) as "LastDayOfCurrentMonth";


CurrentDate
LastDayOfCurrentMonth


2022-10-01
2022-10-31

employeeID	grade	effectiveFrom	#Days
1	A	2022-01-01 00:00:00	14
1	B	2022-01-15 00:00:00	17
2	A	2022-01-01 00:00:00	31
3	A	2022-01-01 00:00:00	14
3	B	2022-01-15 00:00:00	10
3	A	2022-01-25 00:00:00	7