Derived Field for Length of Service 2

[amillis]

I am trying to create a derived field that will show a certain number of leave accrual hours based on an employees length of service. If anyone with over 14 years receives 14 hours how do I write that? Here is what I have:

@DECODE(REPORTS.V_EMPLOYEE.YEARSSERVICE,' 0','8 hrs','1','8 hrs',' 2','8 hrs','3','8 hrs',' 4','10 hrs','5','10 hrs','6','10 hrs','7','10 hrs','8','10 hrs','9','10 hrs','10','12 hrs','11','12 hrs','12','12 hrs','13','12 hrs','14','14 hrs')

I know there has to be an easier way than typing out all each year of service.

THANKS!

 

[mycal]

Here is our derived field for Length of Service

@INT((@YEAR(@NOW-(@DECODE(REPORTS.V_EMPLOYEE.REHIREDATE,NULL,REPORTS.V_EMPLOYEE.HIREDATE,REPORTS.V_EMPLOYEE.REHIREDATE)))*12+@MONTH(@NOW-(@DECODE(REPORTS.V_EMPLOYEE.REHIREDATE,NULL,REPORTS.V_EMPLOYEE.HIREDATE,REPORTS.V_EMPLOYEE.REHIREDATE)))-1)/12) || ' yr(s) and ' || @ROUND(@MOD((@YEAR(@NOW-(@DECODE(REPORTS.V_EMPLOYEE.REHIREDATE,NULL,REPORTS.V_EMPLOYEE.HIREDATE,REPORTS.V_EMPLOYEE.REHIREDATE)))*12+@MONTH(@NOW-(@DECODE(REPORTS.V_EMPLOYEE.REHIREDATE,NULL,REPORTS.V_EMPLOYEE.HIREDATE,REPORTS.V_EMPLOYEE.REHIREDATE)))-1),12),0) || ' mo(s)'

 

[CharlesCook]

I would do it a little different:

@DECODE(REPORTS.V_EMPLOYEE.YEARSSERVICE,
0,'8',
1,'8',
2,'8',
3,'8',
4,'10',
5,'10',
6,'10',
7,'10',
8,'10',
9,'10',
10,'12',
11,'12',
12,'12',
13,'12',
14,'14',
'14') || ' hrs'

You Could also use the case statement but it would not help much in the formula.

case when <condition> then <value>
when <condition> then <value>
...
else <value>
end

See

http://www.databasejournal.com/features/oracle/article.php/3344871

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[amillis]

Thank you so much!!!