Calculating length of service by increment

User_DAALX

Hi There,

I'm trying to calculate incremental anniversary dates using the formula below, but instead of showing 10 as the result, I really want the calculation to bring back the anniversary date i.e 10 + 25/12/2012 should bring back 25/12/2022.

CASE WHEN TIMESTAMPDIFF(SQL_TSI_DAY, "Employee Attributes"."Employee NHS Entry Date", CURRENT_DATE)/365.12 <10 AND (TIMESTAMPDIFF(SQL_TSI_DAY, "Employee Attributes"."Employee NHS Entry Date", CURRENT_DATE)/365.12)+2 >=10 THEN '10' 

Can some one please help me with this

User_DAALX

Gianni Ceresa

Look at the function TIMESTAMPADD and you will be able to get what you are after.

User_DAALX

Thanks for your response, I have tried different TIMESTAMPADD syntaxes but always comes back with an error, I'm new to Oracle BI

Gianni Ceresa

An error ... like what?

What was your formula and what was your error?

User_DAALX

Hi,

I use the syntax below

CASE WHEN TIMESTAMPDIFF(SQL_TSI_DAY, "Employee Attributes"."Employee NHS Entry Date", CURRENT_DATE)/365.12 <10 AND (TIMESTAMPDIFF(SQL_TSI_DAY, "Employee Attributes"."Employee NHS Entry Date", CURRENT_DATE)/365.12)+2 >=10 THEN TIMESTAMPADD(SQL_TSI_YEAR,10, "Employee Attributes"."Employee NHS Entry Date") 

The error when it runs is:

Gianni Ceresa

Ok, the real error is behind the "[+] Error Details" link, if you expand it, you get the real error.

Your expression isn't a valid expression because your CASE WHEN is not closed.

The syntax of a CASE WHEN is:

CASE
  WHEN <condition> THEN <value/expression>
[ WHEN <condition> THEN <value/expression> ]+
[ ELSE <value/expression> ]
END

While additional WHEN-THEN or a ELSE are optional, your CASE WHEN must be closed by END.

User_DAALX

Thanks Gianni, for your comments, it has now worked!

I was testing the first part of my case statement with a timestamp function and running not thinking this would affect the whole of the nested case statements as I have multiple anniversary dates to work on, Now I have a better understanding, thanks again!