Calculating the next anniversary date in HANA Calculation View?

[MattHarding]

Hi All,

Trying to help out the BI folks and give them a reusable way to calculate the upcoming anniversary date for an employee (preferably without using scripted calculation views). e.g. If they started on the 20th of September, 1998; then the next anniversary date (based on today being the 1st of July) is 20th of September, 2016.

I've written various SQL Script scenarios (scalar function, hdb procedure, script calculation view) which all produce the right result by themselves, but I can't figure out the syntax/support to allow me to insert this into an existing Calculation View to change the anniversary date to the next anniversary date.

Unfortunately it looks like Scalar Functions aren't allowed in Computed Columns; and we can't figure out how to take the output of one projection and use it as a parameter input in a scripted calculation view.

Any recommendations to solve this in a reusable way while keeping a single point for the calculation logic?

Just for reference, the SQL Script looks like this (note - I'm limited to SP10 date functions and there may be a better way):

Scalar Function:

FUNCTION "ACME"."Acme.general.functions::Get_Next_Anniversary_Date" ( 
  IN AnniversaryDate DATE ) 
  RETURNS NextAnniversary Date
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER AS
BEGIN
    declare today date;
    declare thisYear integer;
    declare anniversaryYear integer;
    declare difference integer;
    today = TO_DATE(NOW());
    SELECT YEAR (today) "year" into thisYear FROM DUMMY;
    SELECT YEAR (AnniversaryDate) "year" into anniversaryYear FROM DUMMY;
    difference = thisYear - anniversaryYear;
    select add_years(anniversaryDate, difference) into NextAnniversary from DUMMY;
    if NextAnniversary < today then
        select add_years(NextAnniversary, 1) into NextAnniversary from DUMMY;
    end if;  
END;

Scripted Calculation View:

BEGIN 
    declare today date;
    declare thisYear integer;
    declare anniversaryYear integer;
    declare nextAnniversary date;
    declare difference integer;
    today = TO_DATE(NOW());
    SELECT YEAR (today) "year" into thisYear FROM DUMMY;
    SELECT YEAR (AnniversaryDate) "year" into anniversaryYear FROM DUMMY;
    difference = thisYear - anniversaryYear;
    select add_years(anniversaryDate, difference) into nextAnniversary from DUMMY;
    if nextAnniversary < today then
        select add_years(nextAnniversary, 1) into nextAnniversary from DUMMY;
    end if;
    var_out = select TO_DATE(nextAnniversary) as "NextAnniversaryDate" from DUMMY;  
END

Thanks,
Matt

[muthuram_shanmugavel2 · Accepted solution]

Hi Matt,

This my base Table which is having Employee ID and Date of Joining.

Using Calculated Columns, I able to find the Employees anniversary Date for current Year.

Calculated Columns Logic is,

P.S: I am getting error If I write the Logic in single calculated column, So I have spitted as 3 calculated columns.

1. Calculated Column_1: CURRENT_YEAR

    leftstr(now(),4)

2. Calculated Column_2: ANNIVERSARY_DATE_MONTH

    rightstr(string("DOJ"),6)

3. Calculated Column_3: EMPLOYEE_ANNIVERSARY_DATE

    date ("CURRENT_YEAR" + "ANNIVERSARY_DATE_MONTH")

I have make Calculated Column_1 and Calculated Column_2 are hidden fields.

The final Output is,

We are able to get Anniversary Year for employees using calculated columns.

Regards,

Muthuram

[lbreddemann · Accepted solution]

I guess this would work, but I understood the requirement such that the calculation bit should be reusable in different calc views.

And the relatively easy solution to this is: build a SQL view containing your calculation logic and join this view in your calc view.

[MattHarding · Accepted solution]

Muthuram - Lars is right about the requirement, plus I checked and you'll get an error for the 29th of February, 2016 future anniversrary date with this logic; but thanks for sharing this solution.

Lars - Thanks for helping clarify this. I agree the script view does solve this and is my fall back, but was trying to do this as something that can be done further up the chain of a Calculation View and can be represented in the graphical tools (for easy reuse within the BI modelling team).  e.g. A view may have a birthday and work anniversary which you then want to calculate the next anniversary of both of these. Just figured using a scalar function or passing a table column into a variable in another view would be possible, but guessing I'm expected too much.

Cheers,

Matt

FYI - SSO gets me again - Logged me in as my customer user-id for this response!

[lbreddemann · Accepted solution]

You can do just that with a SQL view.

Just add it into a projection as you would with a table and you're good to go.

[MattHarding · Accepted solution]

Ah - If I understand you correctly, it would mean to break up the current query into multiple parts to support the conversion.

e.g. Assuming we don't ask them to change their source data (upsetting the whole view they've crafted), from the calculation view that has both the original birthday and work anniversary, query it within a SQL View using the scalar function to determine the anniversary dates, and then reuse this SQL View as a projection as they add further logic on top of this (e.g. For Smart Business Tiles).

This will work fine, but from a BI developer perspective, they would prefer just to convert the dates in the 1 Calculation View and to not have to change the original data source to a SQL view (which if this can't be done nicely, is what I'll recommend regardless).

Thanks,

Matt

[lbreddemann · Accepted solution]

Well, they won't actually need to change the original view at all.

Let's say your original view is called EMPS.

Then you can have a SQL view that does some sort of calculation, e.g. this one

drop view nextAnniversary;

create view nextAnniversary as

select emp_id, hire_date,

        case

        when  add_days ( to_date( year(current_date) ), dayofyear (hire_date)-1) > current_date then

             add_days ( to_date( year(current_date) ), dayofyear (hire_date)-1)  

        else

            add_years (add_days ( to_date( year(current_date) ), dayofyear (hire_date)-1), 1)

        end  as next_anniversary

from emps

Ok, let's not get into the discussion of whether the calculation is correct (it's not) in this case, but you get the gist.

Take the EMP_ID and the required base date from the EMPS view/table and produce the output consisting of

EMP_ID and NEXT_ANNIVERSARY.

The calc view that is meant to present the information together then simply performs a referential join 1:1:

That all works quite easily from my point of view.

[MattHarding · Accepted solution]

Thanks for the significant detail. I get it now. I guess I come from more of a development perspective, and was trying to avoid another database read since the information was already in one of the views and just needed to be a calculation on that field; but I should just trust the hana optimisation (ignoring how trivial this request is) and I'll show them how to do it like this as it's very straightforward.

Cheers,

Matt

[muthuram_shanmugavel2 · Accepted solution]

Thanks Lars,

I created SQL view containing Anniversary_date Calculation

Create View View_Anniversary_Date As

Select E_ID, DOJ,

case

When add_years (DOJ, (YEAR(CURRENT_DATE) - YEAR(DOJ))) > CURRENT_DATE Then

add_years (DOJ, (YEAR(CURRENT_DATE) - YEAR(DOJ)))

Else

add_years (DOJ, (YEAR(CURRENT_DATE) +1 - YEAR(DOJ)))

End AS Anniversary_Date

from "INF713775"."EMPLOYEE"

Then I used this Graphical CV Projection

Hi Matt,

This is reusable calculation View with anniversary_date field.

So you can use it when it required.

Feb 29 Date also work fine here.

Regards,

Muthuram