Calulate a field depending on years of service 1

[NOLA]

I am trying to create a report that will calculate a field depending on years of service.

I need to determine years of service first then calculate an amount for # of years.

For example if an employee has less than 5 years then I need to calculate a field by 1%
ELSE
IF YEARS IS LT 10 YEARS THAN CALCULATE BY 2%
ELSE
iF YEARS IS lt 15 THEN CALCULATE BY 3%
ELSE
IF YEARS IS LT 20 THEN CALCULATE BY 4%
ELSE
iF YEARS gt 20 THEN CALCULATE BY 5%.

WE ARE ORACLE BASE AND USING REPORTSMITH VERSION 3.10
Can you do this statement using decode?

 

[CharlesCook]

Here is a pretty good write up on the case statement:

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

Try this:

case
when trunc(((Starting Date - Ending Date) / 365.25)) < 5 then 1
when trunc(((Starting Date - Ending Date) / 365.25)) < 10 then 2
when trunc(((Starting Date - Ending Date) / 365.25)) < 15 then 3
when trunc(((Starting Date - Ending Date) / 365.25)) < 20 then 4
else 5
end

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

Did this help?


CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[NOLA]

The following is not working. When I add the comparison >=5 and <10 and also need to multiply the percent * amount.
What is the correct syntax in doing this?

case
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 5 then 0.03
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) >=5 AND < 10 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 15 then 3
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 20 then 4
else 0.08 * "MEMO"."AL_AMOUNT"
end

 

[CharlesCook]

Try this:

("MEMO"."AL_AMOUNT" *
case
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 5 then 0.03
when ((trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) >=5) AND (trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25) < 10)) then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 15 then 3
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 20 then 4
else 0.08
end)

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[NOLA]

Thank you for your help, but I am receiving error message ORA-00907 missing right parenthesis on the following code.


when ((trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) >=5) AND (trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25) < 10)) then 0.04

 

[CharlesCook]

Try this:

("MEMO"."AL_AMOUNT" *
case
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 5 then 0.03
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 5 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 6 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 7 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 8 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 9 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 10 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 15 then 3
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 20 then 4
else 0.08
end)

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[CharlesCook]

Sorry I'm a little fast on the submit button. I think you will need to fix 15 and 20

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[NOLA]

When I added sum to the derived field I am now receiving the following error message.

ORA-00979: not a GROUP BY expression

What causes this?

 

[CharlesCook]

I'm not sure I know what you mean when you say "i added sum to the derived field". Post the new version for me to see.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[NOLA]

Before I only had CEIL then I added sum AND FOLLOWING MESSAGE APPEARS

ORA-00979: not a GROUP BY expression


SUM (CEIL ("MEMO"."AL_AMOUNT" *
case
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 5 then .03
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 5 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 6 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 7 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 8 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 9 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 10 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 10 then 0.05
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 11 then 0.05
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 12 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 13 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) = 14 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 15 then 0.04
when trunc(((SYSDATE - "PERS"."ORIG_HIRE_DT") / 365.25)) < 25 then 0.07
else 0.08
end) )

 

[ReportSmithing]

Uncheck the box for Database Grouping and you should be fine.

 

[NOLA]

Anytime you have a aggregate derived field you need to select database grouping.

 

[CharlesCook]

Database Grouping is somthing that you REALLY need to understand before you get into it. I find way to many people including ADP folks (don't read this as a slam on ADP) that just turn on and off bits without knowing how it all works. I can't teach Database Grouping and aggregate functions in a fourm. You really need a class to get into them. Here is a ppt that can give you a flavor of Database Grouping and aggregate functions using pc payroll. I hope it is of some small help.

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-DatabaseGrouping.pps

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining