MySQL Forums
Forum List  »  Newbie

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 ,

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.


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://dbfiddle.uk/ix7yN4iD

Options: ReplyQuote


Subject
Written By
Posted
Using windows 8 functions
October 07, 2022 01:04PM
October 07, 2022 02:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.