Skip to Content
0
Jul 01, 2016 at 04:18 AM

Calculating the next anniversary date in HANA Calculation View?

389 Views

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