I am creating a Report which takes in a begda and an endda, one of the methods needs to return the amount of unpaid absence leave taken (PA2001) within the selected time period.
The problem is, if I select for example 01.01.2021-30.11.2021 and there is somebody with an unpaid absence from 01.06.2021 - 31.12.2021, the following SQL Statement will return the amount of absence within the whole time period he is absent, not just for the dates selected:
SELECT begda, endda, abwtg FROM pa2001 INTO TABLE @DATA(other_unpaid_leave) WHERE pernr = @pernr AND ( ( subty = '0620' ) OR ( subty = '0600' ) OR ( subty = '0601' ) )
AND ( ( begda >= @begda AND endda <= @endda ) " Sickleave between given dates
OR ( begda <= @begda AND endda <= @endda AND begda >= @endda ) " Sickleave starts before given date, ends during given dates
OR ( begda >= @begda AND endda >= @endda AND begda < @endda ) ).
I need to be able to return the amount of "workdays" within the period, without accounting for Bank/National holidays. Does anybody have a suggestion?