Finding answers on workdays

[lbreddemann]

Cunningham's Law is the idea that "the best way to get the right answer on the internet is not to ask a question; it's to post the wrong answer.".

More often than not I feel that some of the answers provided to questions on the SAP Community platform follow this approach, probably being unaware that there is a name for this strategy.



Question "SAP HANA Exclude weekends while calculating diff between two dates." is such a question.

"I have requirement to find out the Number of hour between two dates, but we have to exclude the weekends (Saturday & Sunday).

Example : DATE1 is 19-July and DATE2 is July-26 - Actual diff is - 168 Hours.

Required Output is (168 - 48 Hours (Saturday & Sunday) - 120 Hours.

This I want to achieve using Graphical or Script Cal view, please help me to solve the issue"

The OP did not bother googling for solutions for this very common problem nor does (s)he seem to have access to classic SQL development literature, like "Jeo Celko's Thinking in Sets".

This is one of those "throw the requirement over the fence" question and really should have been moderated.
However, as so often, an eager answerer offered his help and provided the following code to solve the problem:

DO

BEGIN

    declare v_i integer;

    declare v_count integer;

sel1 = select

            "0CALDAY" as "CALDAY",

            "Yesterday" as "YESTERSDAY",

             sum("DATE_DIFF") AS "DATE_DIFF" ,           

             sum((select 0 from dummy)) as "HOUR_DIFF",

             sum((select 0 from dummy)) as "WRK_HOUR_DIFF"

             from  "_SYS_BIC"."ZTABLE"

--           where "0CALDAY" >= '20180701'

             GROUP BY "0CALDAY", "Yesterday";

 

 

select count(*) into v_count from :sel1;

 

for v_i in 1..v_count do

    WHILE to_date(:sel1.CALDAY[v_i]) < to_date(:sel1.YESTERSDAY[v_i]) do 

        if( weekday(to_date(:sel1.CALDAY[v_i])) ) < 5 

        then

            sel1.WRK_HOUR_DIFF[v_i] = :sel1.WRK_HOUR_DIFF[v_i]  + 24 ;

            sel1.HOUR_DIFF[v_i]     = :sel1.HOUR_DIFF[v_i]      + 24  ;         

        else

            sel1.HOUR_DIFF[v_i]     = :sel1.HOUR_DIFF[v_i]      + 24  ;         

        end if;

        sel1.CALDAY[v_i] = ADD_DAYS(to_date(:sel1.CALDAY[v_i]), 1) ;

             

    end while ; 

end for;

 

select * from :sel1;

 

END;

Reading through this approach several problems stick out:

• this really shouldn't require SQLScript at all and definitively not a loop


• using 'SELECT 0 from DUMMY' to generate a constant value of integer 0 is plain wrong


• summing constant 0 over a grouping set is plain wrong as well - the result is a constant 0


• removing the aggregation allows for removing the grouping altogether: a SELECT DISTINCT would do the trick here


• the number of records in sel1 depends solely on the number of different values in ZTABLE.CALDAY a table that has not been defined


• this number of different CALDAY values is used to loop and add 24 hours to the sel1 table variable HOUR_DIFF and WRK_HOUR_DIFF to keep a tally of hours between dates, which could have been done with HANA SQL functions directly

This looks very much like the kind of SQL code developers tend to write that are not "at home" with SQL and rather try pushing an imperative programming style into their SQL code.

This code is inefficient, slow and hard to maintain.
In short: don't do it like this.

Alternative solutions

The first approach for this type of common problem really should be to not trying to develop the solution yourself, but instead to look for existing standard solutions.
The mentioned book uses so-called auxiliary tables for this (see chapter 9.5 Calendar Tables) and so does SAP NetWeaver and also the freestyle solution presented in "Want to Calculate WORKDAYS_BETWEEN without TFACS Table?" .

The core of this solution approach is to maintain a table of all dates that indicates whether or not a given date is a weekend or not. Such a table could even hold the information if a given date has been a working day or a public holiday.

Looking at the requirement of "excluding weekends" from counting hours being able to discern workdays from holidays seem like a close-by requirement.

Let's stick to the case of simply excluding the weekends which the OP defined as Saturday and Sunday.
When working on such a requirement it is particularly important to consider the calendar context in which the solution should work.
Certain calendars (e.g. Isreali calendar) have Friday and Saturday as their weekends.
When the underlying requirement actually is "hours of working days" this might become more complicated with different calendars. Consider, for example, that daylight saving time shifts effectively add/subtract hours - commonly over weekends, but sometimes also on working days.
The OP hasn't mentioned anything beyond the description of a mechanical calculation problem, so we cannot know what the underlying requirement really was.

Anyway, a quick solution for the simple counting of hours on days other than Saturday and Sunday would be to use the SAP HANA built-in table "_SYS_BI"."M_TIME_DIMENSION".
This table provides dates in different formats such as DATE_SQL as well a day of the week number DAY_OF_THE_WEEK_INT (Monday =0, Sunday=6).

With data generated in the table, the original question can be answered as "number of non-weekend days between the given days multiplied by 24 hours"

select count(*) as no_of_workdays,

count(*) * 24 as no_of_hours

from (

select

date_sql,

day_of_week_int

from "_SYS_BI"."M_TIME_DIMENSION"

where day_of_week not in (5, 6)

and date_sql between to_date('19.07.2018', 'DD.MM.YYYY')

and to_date('26.07.2018', 'DD.MM.YYYY')

)

;



NO_OF_WORKDAYS NO_OF_HOURS

6 144

Note how this differs from the OPs calculation by one day.
Checking the inner query gives us:

DATE_SQL DAY_OF_WEEK_INT

19/07/2018 3

20/07/2018 4

23/07/2018 0

24/07/2018 1

25/07/2018 2

26/07/2018 3

Which means that the OP did miscalculate the hours in the example, by not including the last day of the given range.
This points to another common trap when dealing with dates and intervals: one needs to be clear about whether interval end dates should be included or excluded.

The same approach can be taken with graphical calculation views:

SELECT

count(distinct "DATE_SQL" ) as no_of_workdays,

count(distinct "DATE_SQL" ) * 24 as no_of_hours

FROM "_SYS_BIC"."sandpit/EXCLUDE_DATES";



/*

NO_OF_HOURS

144

*/

The broader view

I mentioned above that this common problem can be broadened to "separate working days from non-working days".
The SAP business applications have been dealing with such problems for many decades now and so it's not surprising that there is indeed a standard solution available for this.
This standard solution comes in the form of "FACTORY CALENDARS".
SAP Netweaver table TFACS holds one row for each year and each calendar.
Every row contains twelve column with a "bitfield" represented by '1' and '0' characters for each day of the month.
A '1' indicates a working day and a '0' denotes a non-working day.

There are some other columns available, but for our purpose, this is what we need to know.

IDENT JAHR MON01 MON02 ... MON12

AU 2018 0111100111110011111001111000111 1100111110011111001111100111 ... 0011111001111100111110010010110

Typically this table is re-used or replicated from the SAP NetWeaver environment so that all date calculations are using the same calendar definitions.
If you want to use the table without an SAP NetWeaver system you could use the following commands to create it yourself:

create row table TFACS

( IDENT NVARCHAR(2) not null -- factory calendar

, JAHR NVARCHAR(4) not null -- year stored

, MON01 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON02 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON03 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON04 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON05 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON06 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON07 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON08 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON09 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON10 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON11 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, MON12 NVARCHAR(31) not null -- Calendar: days in a month, 1 for working day, 0 for non-working day

, BASIS NVARCHAR(5) not null -- start of factory day numbering

, FENUM NVARCHAR(3) not null -- number of days in year

, WENUM NVARCHAR(3) not null -- number of workdays in year

, LOAD NVARCHAR(1) not null -- load year in bufer flag ('X' = YES)

, primary key (IDENT, JAHR));

As an example I entered one row with the working days of Australia/Victoria in 2018:

insert into tfacs values (

'AU', '2018'

-- 1 2 3

-- 1234567890123456789012345678901

, '0111100111110011111001111000111' -- january

, '1100111110011111001111100111' -- february

, '1100111110011111001111100111100' -- march

, '001111100111110011111001011001' -- april

, '1111001111100111110011111001111' -- may

, '100111110001111001111100111110' -- june

, '0111110011111001111100111110011' -- july

, '1110011111001111100111110011111' -- august

, '001111100111110011111001111100' -- september

, '011110011111001111100111110011' -- october

, '11001111100111110011111001111' -- november

, '0011111001111100111110010010110' -- december

, ' '

, ' '

, ' '

, ' ');

With this table in place, SAP HANA provides SQL function WORKDAYS_BETWEEN (see also the SAP HANA Academy video), that returns the desired number of working days directly:

SELECT WORKDAYS_BETWEEN('AU', to_date('19.07.2018', 'DD.MM.YYYY')

, to_date('26.07.2018', 'DD.MM.YYYY')) "no of working days"

FROM dummy;



/*

no of working days

5

*/

Note that here, the "between" condition is understood as "workdays computed starting on the 19th and finishing on, but not including, the 26th" (see documentation on this) which seem to be the understanding of the OP.

The benefits of using calendar tables over pure function approaches are clear:

• it's easy to understand which days are considered working days and which days are not


• multiple calendars can be maintained and used in parallel (different years, countries, union agreements, operating shifts, etc.)


• the SQL/application code does not need to be changed when the calendars need updating

There you go, now you know.

 

---

This blog has been published first on LBREDDEMANN.ORG