Using mysql 8 window functions

I think if it were me, I’d generate a list containing an entry for each day with the effective grade and salary, and then just aggregate at the end. Take a look at this fiddle:

https://dbfiddle.uk/4t2RW2M2

I’ve started with the aggregate query, just so we can see the output, then I break out pieces of the query to show intermediate outputs. Here is an image of the final output and the query generating it:

SELECT grade, gradeEffective, salary, salaryEffective,
  min(dt) as startsOn, max(dt) as endsOn, count(*) as days,
  dailyRate, 
  sum(dailyRate) as pay
FROM (
  SELECT DISTINCT dt, grade, gradeEffective, salary, salaryEffective, 
    ROUND((salary * 12) / 365, 2) as dailyRate
  FROM (
    SELECT dts.dt, 
      first_value(r.grade)         OVER w as grade,
      first_value(r.effectiveFrom) OVER w as gradeEffective,
      first_value(s.salary)        OVER w as salary,
      first_value(s.effectiveFrom) OVER w as salaryEffective
    FROM (
      WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
      SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates
    ) dts
    LEFT JOIN EmployeeSalary s ON dts.dt >= s.effectiveFrom
    LEFT JOIN EmployeeRole r on dts.dt >= r.effectiveFrom
    WINDOW w AS (
      PARTITION BY dts.dt 
      ORDER BY r.effectiveFrom DESC, s.effectiveFrom DESC
      ROWS UNBOUNDED PRECEDING
    )
  ) z
) a GROUP BY grade, gradeEffective, salary, salaryEffective, dailyRate
ORDER BY min(dt);

Now, the first thing I’ve done is create a list of dates using a recursive CTE:

WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates

which produces a list of dates from July 1st to July 31st.

Take that list of dates and left join both of your tables to it, like so:

SELECT *
FROM (
  WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
  SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates
) dts
LEFT JOIN EmployeeSalary s ON dts.dt >= s.effectiveFrom
LEFT JOIN EmployeeRole r on dts.dt >= r.effectiveFrom

with the dt greater than or equal to the effective dates. Notice that after the 9th you start to get duplicate rows for each date.

We’ll create a window to get the first values for grade and salary for each date, and we’ll order first by role effectiveFrom and then salary effectiveFrom, to fulfil your priority condition.

SELECT dts.dt, 
  first_value(r.grade)         OVER w as grade,
  first_value(r.effectiveFrom) OVER w as gradeEffective,
  first_value(s.salary)        OVER w as salary,
  first_value(s.effectiveFrom) OVER w as salaryEffective
FROM (
  WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
  SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates
) dts
LEFT JOIN EmployeeSalary s ON dts.dt >= s.effectiveFrom
LEFT JOIN EmployeeRole r on dts.dt >= r.effectiveFrom
WINDOW w AS (
  PARTITION BY dts.dt 
  ORDER BY r.effectiveFrom DESC, s.effectiveFrom DESC
  ROWS UNBOUNDED PRECEDING
);

This is still going to leave us multiple entries for some dates, although they are duplicates, so let’s use that output in a new query, using DISTINCT to leave us only one copy of each row and using the opportunity to add the daily rate field:

SELECT DISTINCT dt, grade, gradeEffective, salary, salaryEffective, 
  ROUND((salary * 12) / 365, 2) as dailyRate
FROM (
  SELECT dts.dt, 
    first_value(r.grade)         OVER w as grade,
    first_value(r.effectiveFrom) OVER w as gradeEffective,
    first_value(s.salary)        OVER w as salary,
    first_value(s.effectiveFrom) OVER w as salaryEffective
  FROM (
    WITH RECURSIVE dates(n) AS (SELECT 0 UNION SELECT n + 1 FROM dates WHERE n + 1 <= 30)
    SELECT '2022-07-01' + INTERVAL n DAY as dt FROM dates
  ) dts
  LEFT JOIN EmployeeSalary s ON dts.dt >= s.effectiveFrom
  LEFT JOIN EmployeeRole r on dts.dt >= r.effectiveFrom
  WINDOW w AS (
    PARTITION BY dts.dt 
    ORDER BY r.effectiveFrom DESC, s.effectiveFrom DESC
    ROWS UNBOUNDED PRECEDING
  )
) z;

This produces the deduplicated daily data

and now all we have to do is use aggregation to pull out the sums for each combination of grade and salary, which is the query that I started off with.

Let me know if this is what you were looking for, or if anything is unclear.

Since the start and end conditions weren’t fleshed out in the question, I just created the date list arbitrarily. It’s not difficult to generate the list based on the first effectiveFrom in both tables, and here is an example that runs from that start date until current:

WITH RECURSIVE dates(n) AS (
  SELECT min(effectiveFrom) FROM (
    select effectiveFrom from EmployeeRole UNION
    select effectiveFrom from EmployeeSalary
  ) z
  UNION SELECT n + INTERVAL 1 DAY FROM dates WHERE n <= now()
)
SELECT n as dt FROM dates

I also didn’t handle for multiple employees, since there was only one given and I would just be guessing at the shape of the actual data.