Calculate Working Days Count using SQLScript on SAP HANA Database
SAP HANA SQLScript developer can create SQL function to calculate working days count between two dates using the SQL codes shared in this tutorial. SQL function for SAP HANA programmers which takes two input date arguments and returns the count of work days which are the weekdays by default as output. The SQLScript function CalculateWorkingDaysCount uses SQL date functions intensively and includes a WHILE loop.
Below SQL function has two input arguments, starting date and end date parameters.
CalculateWorkingDaysCount SQLScript function returns the number of working days between two dates provided as input arguments to the SAP HANA database function.
The working day classification for following SQLScript function is done by considering weekdays.
I mean if the date is Monday, Tuesday, Wednesday, Thursday or Friday, then the SQL function increases the working days count by one.
Otherwise, if the date is for a day of Saturday or Sunday, it assumes it is a non-work day or holiday.
CREATE function CalculateWorkingDaysCount (startdate date, enddate date)
returns workingDays integer
language sqlscript as
begin
declare i int;
workingDays := 0;
i := days_between(:startdate, :enddate);
while :i >= 0
do
if weekday( add_days(:startdate,:i) ) < 5
then
workingDays := :workingDays + 1;
end if;
i := :i - 1;
end while;
end
Of course, it is important to consider following assumptions:
On your SAP HANA database the first day of a week should be configured as Monday (actually week day 0).
If the weekends are Saturday and Sunday.
If you have a different calendar, you should modify the CalculateWorkingDaysCount SQL function according to your requirements.
Following SQLScript shows how to use CalculateWorkingDaysCount user function with sample data.
The output shows working days count in January and February in different columns.
If you have a company working days calendar, additionally a department calendar, of course if you plan to consider the employee holidays, then you can bring additional checks into the WHILE loop in your SQLScript function.
The sample SQLScript function shared in this tutorial for SAP HANA developers uses following SQL functions and structures:
DAYS_BETWEEN SQLScript date function to calculate difference between two dates in times of day.
WEEKDAY SAP HANA date function, to return the week day number of a given date by starting from Monday as 0 to Sunday as 6.
ADD_DAYS SQL date function is used to return a new date calculated by adding a given variable days on a given date value, like 5 days later than today, etc.
And SQLScript WHILE loop to repeat execution of contained SQL code block when the repeat condition is true.
For SQLScript developers, official documentation on SQL Date functions can be viewed at SAP Help Portal
