Skip to Content

After my blog on introducing “Grouping Sets“, I would like to introduce the WorkdaysBetween and AddWorkdays functions. This blog would provide a basic idea about these functions and how to write simple SQL queries using these functions.

In order to work with these functions, table TFACS (Factory calendar (display)) in ECC, should be replicated to SAP HANA. The TFACS table stores days in binary format ex. 010101000010100101, where 0 indicates a non-working day while 1 is a working day.

Below is a screenshot of the output of TFACS table:

/wp-content/uploads/2015/01/1_png_628791.jpg

The WorkdaysBetween function:

As the name suggests, the WorkdaysBetween function is used to compute the number of working days in between two dates for a particular country. The inputs for this function are:

  • Country
  • from date
  • to date
  • Schema name, in which the table TFACS has already been replicated.

The syntax of the WorkdaysBetween function is:

workdays_between(<Country name>, <from date>, <to date>, <schema name>)

For example, we would try to compute number of workdays between 01-01-2014 to 01-01-2015 for US. Hence, the SQL for this would be:

/wp-content/uploads/2015/01/2_628792.png

The table TFACS has been replicated to ‘HANA_SP7_01’ schema in the above example.

The AddWorkdays function:

The AddWorkdays function is used to compute the next working date on adding a specific number of days to a particular date for a particular country. The inputs to the AddWorkdays function are:

  • Country
  • from date
  • number of days
  • schema name, in which table TFACS has been replicated.

The syntax for AddWorkdays function is:

add_workdays(<Country name>, <from date>, <number of days>, <schema name>)

For example, we would try to compute the 16th working day since 01-01-2014 for US. Hence, the SQL for this would be:

/wp-content/uploads/2015/01/1_628793.png

As mentioned earlier, the table TFACS had already been replicated to ‘HANA_SP7_01’ schema in the above example.


Hope this blog aided in better understanding of functions: WorkdaysBetween and AddWorkdays, which could be useful in dealing with various computations involving dates. I would appreciate your feedback on usefulness of this blog



To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Stephen Lasham

    Hi, I note from this that the schema name is one of the parameters.  When I write an SQL script using ADD_WORKDAYS, if I leave the schema name out then the script will run fine from within SAP HANA Studio in the Modeler perspective.  If however I create a stored procedure and try and run it from a Crystal Report then it fails. 

    When run from Crystal Reports without the schema name specified it fails because it cannot find table TFACS, because it looks in my user schema, not the physical schema containing the tables.  To overcome this the schema must be specified within the stored procedure.  If your physical schema name varies between dev, test, quality assurance and production environments, this means you cannot hard code the schema name.  Instead you must use a variable and set it from the schema mapping table.  In my case this was done with the following code.

    Declare V_Schema varchar(20);
    
    select physical_schema into V_Schema from "_SYS_BI"."M_SCHEMA_MAPPING" where authoring_schema = 'SAP_CRM';
    

    Now I have the schema name in variable V_Schema, I can use it for add_workdays and workdays_between as follows

    Select add_workdays('Z3',a.Technical_descision_date,2,V_Schema) as TD_plus_2_Days
    	, workdays_between('Z3', a.Technical_descision_date, current_date,V_Schema) as Work_days_since_TD_Date	
    from table;

    Now when the code is transported from one environment to the other the correct schema name will be used.

    (0) 

Leave a Reply