Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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://dbfiddle.uk/ix7yN4iD
Posted
Comments
Member 8428760 10-Oct-22 16:43pm    
In the partition by section did you mean to pull both effective from dates from er?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900