Sum After Anniversary Date

[vinsavant]

I need to calculate the total number of personal time off hours (PTOs) an employee has taken since his last employment anniversary date. For example, the calculation should take into account the Hire Date of the employee and when was the last anniversary date for this employee and then return the sum of the all the hours taken as of the day of the query by the employee.



I have a table that store the EmpID, Name, HireDate and a second table the stores NumberOfPTOHours, DateTaken, EmpID, etc.

Any help would be greatly appreciated.

Thanks!

[jzwp11]

You will need to use the hiredate to create the anniversary date. The trick is to pick the correct year for the anniversary date. For example, if we want to run the query today (2/18/2013) and the employee's anniversary date is April 1st, we have to use 4/1/2012 as the anniversary date, but if the anniversary date is February 1st we have to use this year (2/1/2013). So in words, if the anniversary occurs after today, then the anniversary date must use last year's year. If the anniversary occurred before today, then use the current year.

Since we have a condition, we will need the IIF() function. We will also need the dateserial() function as well as the year(), month(), day(), date() and dateadd() functions

The following expression calculates the anniversary date relative to the current date:

IIF(dateserial(year(date()), month(hiredate),day(hiredate))>date(),dateserial(y ear(dateadd("yyyy",-1,date())),month(hiredate),day(hiredate)),dateseri al(year(date()),month(hiredate),day(hiredate)))


Now in terms of your query, you need to find the dates between the date the query is run (i.e. the current date) and the anniversary date. So in words you want the records BETWEEN today and anniversary date, so the WHERE clause of the query would look like this:


WHERE datetaken BETWEEN date() AND IIF(dateserial(year(date()), month(hiredate),day(hiredate))>date(),dateserial(y ear(dateadd("yyyy",-1,date())),month(hiredate),day(hiredate)),dateseri al(year(date()),month(hiredate),day(hiredate)))

The expression in red is just the expression for the anniversary date

[vinsavant]

Thank you so much for your prompt and valuable information. It works great.
Regards,

You will need to use the hiredate to create the anniversary date. The trick is to pick the correct year for the anniversary date. For example, if we want to run the query today (2/18/2013) and the employee's anniversary date is April 1st, we have to use 4/1/2012 as the anniversary date, but if the anniversary date is February 1st we have to use this year (2/1/2013). So in words, if the anniversary occurs after today, then the anniversary date must use last year's year. If the anniversary occurred before today, then use the current year.

Since we have a condition, we will need the IIF() function. We will also need the dateserial() function as well as the year(), month(), day(), date() and dateadd() functions

The following expression calculates the anniversary date relative to the current date:

IIF(dateserial(year(date()), month(hiredate),day(hiredate))>date(),dateserial(y ear(dateadd("yyyy",-1,date())),month(hiredate),day(hiredate)),dateseri al(year(date()),month(hiredate),day(hiredate)))


Now in terms of your query, you need to find the dates between the date the query is run (i.e. the current date) and the anniversary date. So in words you want the records BETWEEN today and anniversary date, so the WHERE clause of the query would look like this:


WHERE datetaken BETWEEN date() AND IIF(dateserial(year(date()), month(hiredate),day(hiredate))>date(),dateserial(y ear(dateadd("yyyy",-1,date())),month(hiredate),day(hiredate)),dateseri al(year(date()),month(hiredate),day(hiredate)))

The expression in red is just the expression for the anniversary date

[jzwp11]

You're welcome