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 ,
employeeId grade roleEffectiveFrom
19966 grade 3 2022-07-01
19966 grade 2 2022-07-10
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.
What I have tried:
This query,
SELECT er.employeeId,
es.salary,
`grade`,
date(er.effectiveFrom) roleEffectiveFrom,
date(es.effectiveFrom) salaryEffectiveFrom,
DATEDIFF(LEAST(COALESCE(LEAD(er.effectiveFrom)
OVER (PARTITION BY er.employeeId ORDER By er.effectiveFrom),
DATE_ADD(LAST_DAY(er.effectiveFrom),INTERVAL 1 DAY)),
DATE_ADD(LAST_DAY(er.effectiveFrom),INTERVAL 1 DAY)),
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: