gringope24
Member
- Local time
- Today, 14:49
- Joined
- Apr 1, 2020
- Messages
- 51
Hello Folks,
I work on application to keep the worktime of employees and to calculate their costs.
Employees have different salaries depend on the date. In below table it is clearly shown:
Worktime is keep in separate table
I create the below query to list what salaries should be assign to each date. Unfortunetely the results are not satisfying because in date 2021.07.08 salary should be 125, but according to query result it is 100.
I will appreciate you advices how to fix this issue.
I work on application to keep the worktime of employees and to calculate their costs.
Employees have different salaries depend on the date. In below table it is clearly shown:
Worktime is keep in separate table
I create the below query to list what salaries should be assign to each date. Unfortunetely the results are not satisfying because in date 2021.07.08 salary should be 125, but according to query result it is 100.
SQL:
SELECT tblWorktime.EmployeeFK, tblWorktime.ProjectFK, tblWorktime.WorkTime, tblWorktime.WorkDate, tblSalaries.SalaryCost, tblSalaries.ValidFrom
FROM tblWorktime
LEFT JOIN tblSalaries
ON tblWorktime.EmployeeFK = tblSalaries.EmployeeFK
WHERE tblSalaries.ValidFrom <= tblWorktime.WorkDate
AND NOT EXISTS (SELECT 0
FROM tblSalaries p2
WHERE p2.EmployeeFK = tblSalaries.EmployeeFK
AND p2.ValidFrom <= tblWorktime.WorkDate
AND p2.ValidFrom > tblSalaries.ValidFrom);
I will appreciate you advices how to fix this issue.