MySQL Forums

Using windows 8 functions
Posted by: Ayub Ahmed
Date: October 07, 2022 01:04PM

My salary table looks like ,

employeeId Salary salaryEffectiveFrom
19966 10000.00 2022-07-01
19966 20000.00 2022-07-15

My role/grades table looks like ,

I am trying to get the salary a grade is paid for by taking into account the effective from date in both tables.

grade 3 is effective from 1-July-2022. grade 2 is effective from 10th July, implying grade 3 is effective till the 9th of July i.e. 9 days.
grade 2 is effective from 10-July-2022 onwards.

Salary of 10000 is effective from 1-July-2022 till 14-July-2022 as the salary of 20000 is effective from the 15th. Therefore grade 3 had a salary of 10000 for 9 days, grade 2 salary of 10000 for 4 days with grade 2 with a salary of 20000 from the 10th onwards. The role effectivefrom
date takes precedence over the salary effectivefrom date.

This query,

SELECT er.employeeId,
es.salary,
date(er.effectiveFrom) roleEffectiveFrom,
date(es.effectiveFrom) salaryEffectiveFrom,
OVER (PARTITION BY er.employeeId ORDER By er.effectiveFrom),
er.effectiveFrom) as '#Days' ,
ROUND((salary * 12) / 365, 2) dailyRate
FROM EmployeeRole er
join EmployeeSalary es ON (es.employeeId = er.employeeId)
and er.employeeId = 19966
;

gives me the result set shown below,

employeeId Salary grade roleEffectiveFrom salaryEffectiveFrom Days dailyRate
19966 10000.00 grade 3 2022-07-01 2022-07-01 0 328.77
19966 20000.00 grade 3 2022-07-01 2022-07-15 9 657.53
19966 10000.00 grade 2 2022-07-10 2022-07-01 0 328.77
19966 20000.00 grade 2 2022-07-10 2022-07-15 22 657.53

grade3 is effective for 9 days in July so I want to get the total salary for those 9 days using a daily rate column, 328.77 * 9 = 2985.93 as a separate column but i am unable to do as i am getting the days for the wrong row i.e. 9 should should be the result for the first row.

[dbfiddle][1]

[1]: https://dbfiddle.uk/ix7yN4iD