Using WorkdaysBetween and AddWorkdays function

[former_member201183]

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:

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:

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 16^th working day since 01-01-2014 for US. Hence, the SQL for this would be:

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