Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieving records with overlapping date ranges

Former Member
0 Kudos

Hi All,

I am dealing with a somewhat tricky situation. I have an internal table containing Column1, Column2, pernr, begda and endda. I now need to retrieve those records from this internal table, that have

i) identical values for Column1 and Column2 AND

ii) the date ranges are overlapping

For example, my table might have these values:

Column1 | Column2 | Pernr | Begda | Endda

-


1 | A | 10 | 1.6.2007 | 30.6.2007

2 | B | 11 | 1.1.2008 | 30.6.2008

2 | B | 12 | 1.3.2008 | 31.12.9999

2 | B | 13 | 1.7.2008 | 31.12.2008

2 | B | 14 | 1.1.2009 | 31.12.2009

So in this case, my program should display rows 2-5, since they have identical values on columns 1 and 2, and also all the date ranges are overlapping.

Can anyone suggest an efficient, performance-oriented logic for this?

I am particularly getting stuck at retrieving record 5, since the date range doesn't overlap with record 4 but overlaps with record 3.

Any ideas in this direction, would be highly appreciated.

Thanks and Regards,

Vidya.

1 ACCEPTED SOLUTION

raymond_giuseppi
Active Contributor
0 Kudos

Try something like

* sort by pernr and begin date
SORT itab BY pernr begda.
CLEAR: previous, previous_index.
LOOP AT itab INTO record.
* if same key
  IF record-pernr = previous-pernr
* and same value
  AND record-fielda = previous-fielda
  AND record-fieldb = previous-fieldb
* and overlapping (remember sorted by begda)
  AND record-begda LE previous-endda.
* - delete obsolete record
    DELETE itab. " implicit index
* - if end later, update previous record
    IF previous-endda LT record-endda.
      previous-endda = record-endda.
      MODIFY itab FROM previous INDEX previous_index.
    ENDIF.
* else memorize previous record
  ELSE.
    previous = record.
    previous_index = sy-tabix.
  ENDIF.
ENDLOOP.

Regards

4 REPLIES 4

raymond_giuseppi
Active Contributor
0 Kudos

Try something like

* sort by pernr and begin date
SORT itab BY pernr begda.
CLEAR: previous, previous_index.
LOOP AT itab INTO record.
* if same key
  IF record-pernr = previous-pernr
* and same value
  AND record-fielda = previous-fielda
  AND record-fieldb = previous-fieldb
* and overlapping (remember sorted by begda)
  AND record-begda LE previous-endda.
* - delete obsolete record
    DELETE itab. " implicit index
* - if end later, update previous record
    IF previous-endda LT record-endda.
      previous-endda = record-endda.
      MODIFY itab FROM previous INDEX previous_index.
    ENDIF.
* else memorize previous record
  ELSE.
    previous = record.
    previous_index = sy-tabix.
  ENDIF.
ENDLOOP.

Regards

0 Kudos

Hi Raymond,

Thanks for your idea. I didn't understand why we are replacing the previous record endda with current record endda if previous endda is less than current endda.

Thanks and Regards,

Vidya.

Edited by: Vidya D on Jul 8, 2008 7:50 PM

0 Kudos

if both intervals overlap, then it extends the first interval (which begin first) to the end of the second interval.

So at the end of treatment, the interval are "optimized"

Regards

Former Member
0 Kudos

Thanks a lot Raymond for leading me in the right direction!

Regards,

Vidya.