Select statement to get date ranges

[siongchao_ng]

Hi all,

I have a specific HR programming which I use typical PNP selection need to grab all records of infotype 0 'unpaid leave'.

Below is my statement to grab all these:

GET pernr.

*-- Processing

PROVIDE * FROM p0001 "ename pernr

• FROM p0000 "massn massg begda endda

• FROM p9759 "pernr begda endda compplan

BETWEEN pn-begda AND pn-endda.

MOVE p0001-ename TO ls_data-ename.

MOVE p0001-pernr TO ls_data-pernr.

MOVE p0000-massn TO ls_data-massn.

MOVE p0000-massg TO ls_data-massg.

MOVE p0000-begda TO ls_data-begda00.

MOVE p0000-endda TO ls_data-endda00.

MOVE p9759-pernr TO ls_data-pernr9759.

MOVE p9759-begda TO ls_data-begda9759.

MOVE p9759-endda TO ls_data-endda9759.

MOVE p9759-compplan TO ls_data-compplan.

APPEND ls_data TO lt_data.

• Find all infotypes 0000 records of type Unpaid Leave

DELETE lt_data WHERE massn <> 'ZA'.

DELETE lt_data WHERE pernr9759 is INITIAL.

CLEAR ls_data.

ENDPROVIDE.

END-OF-SELECTION.

Question:

1. How you use select/loop the table lt_data to find a 9759 record existed before the Unpaid Leave but does not exist after the Unpaid Leave?

[Former Member]

Hi,

First try to get the records of P9759 for Unpaid Leave , you'll have the Begda and Endda dates.

Now read the structure P9759 with Endda = Begda - 1. This should give record before the Unpaid Leave , and

if you read the structure P9759 with begda = Endda + 1 , this will give record after the Unpaid Leave if it's present.

Regards,

Srini.

[siongchao_ng]

Hi Srini Vas,

Thanks for reply. According to you:

First try to get the records of P9759 for Unpaid Leave , you'll have the Begda and Endda dates.

Now read the structure P9759 with Endda = Begda - 1. This should give record before the Unpaid Leave , and

if you read the structure P9759 with begda = Endda + 1 , this will give record after the Unpaid Leave if it's present.

Okie, the first part "get the records of P9759 for Unpaid Leave , you'll have the Begda and Endda dates" is done by my provide...endprovide statement shown in my first post, am i right?

For your info, my lt_data is type table of the following:

TYPES: BEGIN OF data_struct,

ename TYPE p0001-ename,

pernr TYPE p0001-pernr,

massn TYPE p0000-massn, " action

massg TYPE p0000-massg, " reason

begda00 TYPE p0000-begda,

endda00 TYPE p0000-endda,

exception TYPE string, " exception type

pernr9759 TYPE p9759-pernr,

begda9759 TYPE p9759-begda, " begda

endda9759 TYPE p9759-endda, " endda

compplan TYPE p9759-compplan, " compensation plan

END OF data_struct.

Now, the 2nd part, "read the structure P9759 with Endda = Begda - 1.", do you mean as below:

LOOP AT lt_data INTO ls_data.

IF ls_data-endda9759 < ls_data-begda00.

APPEND ls_data to lt_before.

ENDIF.

CLEAR ls_data.

ENDLOOP.

Now the 3rd part, "read the structure P9759 with begda = Endda + 1" do you mean as below:

LOOP AT lt_before INTO ls_data.

IF ls_data-begda9759 > ls_data-endda00.

APPEND ls_data to lt_after.

ENDIF.

CLEAR ls_data.

ENDLOOP.

Edited by: Siong Chao on Dec 9, 2010 7:23 AM

[Former Member]

Hi,

Your initial provide .. endprovide might not get all the records as the records are filtered on begda , endda.

Its better to get the records from the structure P9759 ..

loop at P9759 where endda < Unpaid_record-begda.

*get these records.

endloop.

similarly get the other records.

Regards,

Srini.

[siongchao_ng]

Hi Srini Vas,

loop at P9759 where endda < Unpaid_record-begda.

*get these records.

endloop.

Let say I do not want to use provide...endprovide. I use the PNP as my LDB for my report which will have a selection screen which user can enter the pernr and dates like begda and endda. So based on this, how do you first get to the Unpaid_record-begda?

Note: the unpaid_record came from p0000 with a certain action, let assume = 'ZZ'.

[jpoloteijeiro]

I'm not entirely sure if this is what you need, but I would do the following (getting rid of the PROVIDE statements):

1. Loop through the table for infotype 0000 and selecting only the records that define "unpaid leaves".

2. Calculate the date before the beggining of the unpaid leave as p0000-begda - 1.

3. Look for a record of the infotype 9759 in that date.

4. Calculate the date after the end of the unpaid leave as p0000-endda + 1.

5. Look for a record of the infotipe 9759 in that date.

The code would be as following:

REPORT zsdn.

TABLES: pernr.

TYPE-POOLS: abap.

INFOTYPES: 0000,
           9759.

DATA: lv_date_before_unpaid_leave TYPE d,
      lv_record_before_unpaid_leave TYPE boolean,
      lv_date_after_unpaid_leave TYPE d,
      lv_record_after_unpaid_leave TYPE boolean.

START-OF-SELECTION.
  GET pernr.
    LOOP AT p0000 WHERE massn = 'ZA'. " Action for the unpaid leave
      lv_date_before_unpaid_leave = p0000-begda - 1.
      rp-provide-from-frst p9759 space lv_date_before_unpaid_leave lv_date_before_unpaid_leave.
      IF pnp-sw-found = 0.
        lv_record_before_unpaid_leave = abap_true.
      ELSE.
        lv_record_before_unpaid_leave = abap_false.
      ENDIF.
      lv_date_after_unpaid_leave = p0000-endda + 1.
      rp-provide-from-frst p9759 space lv_date_after_unpaid_leave lv_date_after_unpaid_leave.
      IF pnp-sw-found = 0.
        lv_record_after_unpaid_leave = abap_true.
      ELSE.
        lv_record_after_unpaid_leave = abap_false.
      ENDIF.
      IF lv_record_before_unpaid_leave = abap_true AND
         lv_record_after_unpaid_leave = abap_false.
        " Do whatever you want here
      ENDIF.
    ENDLOOP.