Neep Help With Nested IIf Query???

[Ezyflo]

I have a query that calculates the years of employment for employees based on their start date.

From the years of employment, I would like to create a query that will automatically determine their vacation time.

Year 0 - Vac 0
Year 1 - Vac 80
Year 2-3 - Vac 96
Year 4-10 - Vac 144
Year 11-15 - Vac 168
Year 16+ - Vac 192

I am not sure how to write this query. Any help would greatly be appreciated.

Thanks!

 

[rac2]

There are probably many ways to accomplish this. Here is what I might do.

Make a table named VacationHours
with columns from, to, and hours. Assume the query that calculates years of employment is name EmploymentHistory and that it provides numbers of the same type as the from and to columns, say integers.

SELECT e.Years,
(SELECT hours FROM VacationHours
WHERE e.Years BETWEEN from AND to)
FROM EmploymentHistory e

 

[r937]

yeah, me too, rac2

but use a left outer join, so that you don't have to have table entries for any years over 16

also, instead of from/to, just have one row for each year from 0 through 15

[tt]select e.years
, iif(isnull(v.vac),192,v.vac) as vac
from EmploymentHistory e
left outer
join VacationHours v
on e.years = v.vac[/tt]

rudy

 

[Ezyflo]

Thanks for your help! Works fine.

 

[raskew]

Using the Employees table in Northwind, the following query provides Vacation Time earned as of the anniversary of their employment:

SELECT Employees.LastName, Employees.FirstName, Employees.HireDate, DateSerial(Year(Date()),Month([HireDate]),Day([hiredate])) AS AnnivDate, DateDiff("yyyy",[HireDate],[AnnivDate]) AS NumYears, IIf([numYears]>=16,192, iif([NumYears]=0,0,Choose([NumYears],80,96,96,144,144,144,144,144,144,144,168,168,168,168,168))) AS VacTime
FROM Employees;