How to add 35 days for each year starting specific date?

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

Greeting,

I have a database for vacations. In this db, I want to calculate the balance
for each employee. Each employee has either 35 or 30 days for each year. What
I want to do is calculating the total number of vacation for the employee
form the hiring date up to today date . for example: employee hire date is
1/1/2005, so he has 140 vacation day? How to do that??
 
Jon said:
Greeting,

I have a database for vacations. In this db, I want to calculate the
balance
for each employee. Each employee has either 35 or 30 days for each year.
What
I want to do is calculating the total number of vacation for the employee
form the hiring date up to today date . for example: employee hire date is
1/1/2005, so he has 140 vacation day? How to do that??


Assuming that there is a field in the employees table that stores the number
of days each employee is due each year (called VacationDaysPerYear in the
following example), and assuming that the calculation is to be based only on
whole years, the following query would work ...

SELECT Employees.EmployeeID,
(DateDiff("yyyy",[HireDate],Date()))*[VacationDaysPerYear] AS
VacationDaysDue
FROM Employees;

This does not take into account the possibility that the number of days each
employee is due per year may change over time.
 
Back
Top