SolvedSalary cost for specific date / Valid From issue (1 Viewer)

gringope24

Member
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.

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.

jdraw

Super Moderator
Staff member
Why??Where is the data that supports this?
in date 2021.07.08 salary should be 125
Can you also tell us why the salary changes almost on a daily basis?

gringope24

Member
Why??Where is the data that supports this?
Here:

Can you also tell us why the salary changes almost on a daily basis?
Entered data is for tests purposes only.

Hi

plog

Banishment Pending
July 1, 2020 occurs before June 1, 2021

June 1, 2021 is the closest date before July 8, 2021 in the SalaryCost table.

Attachments

• work_work.accdb
632 KB · Views: 348

gringope24

Member
July 1, 2020 occurs before June 1, 2021

June 1, 2021 is the closest date before July 8, 2021 in the SalaryCost table.
Oh gosh, I realized that by mistake I put 2020 instead of 2021! Thank you for pointing it out! It seamds that query works!

Thank you for your proposition. It is another way to do it!

Replies
2
Views
183
Replies
7
Views
419
Replies
2
Views
359
Replies
11
Views
436
Replies
17
Views
577