Using mysql 8 window functions-CodePudding

My salary table looks like this,

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

My role/grades table looks like this,

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 date in both tables.

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

A 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 be the result for the first row.

dbfiddle

CodePudding user response：

merge the 2 table dates, lead them then use correlated sub queries

with cte as
(
SELECT employeeid,effectivefrom from EMPLOYEEROLE
union  
select employeeid,effectivefrom from employeesalary
)
,cte1 as
(select employeeid,effectivefrom,
          coalesce(
            date_sub(lead(effectivefrom) over (partition by employeeid order by effectivefrom),interval 1 day) ,
            now())  nexteff
from cte
)
select *,
        datediff(nexteff,effectivefrom)   1 diff, 
      (select grade from employeerole e where e.effectivefrom <= cte1.effectivefrom order by e.effectivefrom desc limit 1) grade,
      (select salary from employeesalary e where e.effectivefrom <= cte1.nexteff order by e.effectivefrom desc limit 1) salary
from cte1;


 ------------ --------------------- --------------------- ------ --------- -------- 
| employeeid | effectivefrom       | nexteff             | diff | grade   | salary |
 ------------ --------------------- --------------------- ------ --------- -------- 
|      19966 | 2022-07-01 00:00:00 | 2022-07-09 00:00:00 |    9 | grade 3 |  10000 |
|      19966 | 2022-07-10 00:00:00 | 2022-07-14 00:00:00 |    5 | grade 2 |  10000 |
|      19966 | 2022-07-15 00:00:00 | 2022-10-08 08:51:49 |   86 | grade 2 |  20000 |
 ------------ --------------------- --------------------- ------ --------- -------- 
3 rows in set (0.003 sec)

CodePudding user response：

You can start adding two new columns (i.e. tmpFrom and tmpTo), which should give the correct dates which are needed to calculate the 9 Days.

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,
    date(er.effectiveFrom) tmpFrom,
    (select e2.effectiveFrom 
           from EmployeeRole e2
           where e2.employeeId = er.employeeId and e2.effectiveFrom > er.effectiveFrom
           order by e2.effectiveFrom
           limit 1) as tmpTo
FROM EmployeeRole  er 
join EmployeeSalary es ON (es.employeeId = er.employeeId)
      and er.employeeId = 19966
order by er.effectiveFrom
;

In above query I used a sub-select, which might hurt performance. You can study Window Function, and check if there is a function which suits your needs better than this sub-query.

It's up to you to calculate the number of days between those two columns, but you should also solve the NULL value which should be end of month (But I am not sure if I remember your problem correctly...)

see: DBFIDDLE