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

gringope24

Member
Local time
Today, 18:28
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:
1629218962619.png


Worktime is keep in separate table
1629219077661.png


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.

1629219536451.png


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
Local time
Today, 12:28
Joined
Jan 23, 2006
Messages
14,328
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?
 

mike60smart

Registered User.
Local time
Today, 17:28
Joined
Aug 6, 2017
Messages
1,183
Hi
Can you upload a zipped copy of your database?
 

plog

Banishment Pending
Local time
Today, 11:28
Joined
May 11, 2011
Messages
10,931
July 1, 2020 occurs before June 1, 2021

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:28
Joined
May 7, 2009
Messages
16,504
see Query1 (your final query).
see also qryEffectiveSalary, which uses Calculated column to show "ValidTo" date.
 

Attachments

  • work_work.accdb
    632 KB · Views: 356

gringope24

Member
Local time
Today, 18:28
Joined
Apr 1, 2020
Messages
51
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!

see Query1 (your final query).
see also qryEffectiveSalary, which uses Calculated column to show "ValidTo" date.
Thank you for your proposition. It is another way to do it!
 

Users who are viewing this thread

Top Bottom