Skip to Content
0

ABAP SQL capture records of a date range of active employees

Sep 27, 2017 at 04:21 AM

38

avatar image
Former Member

Record in pa0000 / pa0001 has two records as follows:

Begda Endda

03.07.2017 31.12.9999

03.01.2017 02.07.2017

Selection screen has date range : Low: 01/07/2017 and High: 31/07/2017

ABAP Code has written as follows:

 Select data from Pa0001 table
  SELECT PERNR
         ENDDA
         BEGDA
         PERSG
         PERSK
    FROM PA0001
    INTO TABLE T_PA0001
    WHERE PERNR IN S_PERNR[] AND
          BEGDA <= S_BUDAT-LOW AND
          ENDDA >= S_BUDAT-HIGH AND
          PERSG IN S_EMPGR[] AND
          PERSK IN S_EMPSG[] AND
          GSBER IN S_WERKS AND
         BTRTL = 'FURC' .

The aforesaid two records are not capturing.

I would like to re-write the code by using method "Exclude all wrong options" instead of present method "enlist all acceptable options", as follows.

WHERE NOT ( pa0001-begda > s_budat-high or pa0001-endda < s_budat-low)

Any help in this regard will be highly appreciated.

With Warm Regards,

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Raymond Giuseppi
Sep 27, 2017 at 06:59 AM
1

If I guesss your actual requirement, a correct check for selecting overlapping intervals would have been (begin before the end and end after the start of the other interval)

" BEGDA LE S_BUDAT-LOW AND ENDDA GE S_BUDAT-HIGH is false as it only selects intervals that contain the whole selection
BEGDA LE S_BUDAT-HIGH AND ENDDA GE S_BUDAT-LOW " is correct as it selects interval that overlap the selection

Don't forget that an exclude criteria wont usually get good performances.

NOT ( BEGDA GT S_BUDAT-HIGH OR ENDDA LT S_BUDAT-LOW )" is correct but poor performance to be expected
Share
10 |10000 characters needed characters left characters exceeded
Sandra Rossi Sep 27, 2017 at 06:05 AM
0

So, your question is how to get the lines from PA0001 which don't overlap at all the date interval S_BUDAT-LOW to S_BUDAT-HIGH ?

For this, do :

WHERE BEGDA > S_BUDAT-HIGH OR ENDDA < S_BUDAT-LOW
Share
10 |10000 characters needed characters left characters exceeded