topic Re: select rows containing dateto and datefrom from table in Application Development and Automation Discussions

select rows containing dateto and datefrom from table

Former Member — Tue, 28 Mar 2006 13:37:01 GMT

Hi everybody.

I have a table containing a datefrom and date to for every row.

Now I need to select a subset of this table that contains all the data between datefromX and dateto_x. With all the data this implies that I also need the periods that are only

partially between the two dates…

Graphical illustration of the problem:

Table:

Row_a – datefrom_a – dateto_a,

Row_b – datefrom_b – dateto_b,

Row_c – datefrom_c – dateto_c,

Row_d – datefrom_d – dateto_d,

Need every field within the range datefrom_x and dateto_x.

B, c and d.

<x>

In this situation with datefrom_x and dateto_x, I would need a subset of the table looking as follows:

Row_b – datefrom_b – dateto_b,

Row_c – datefrom_c – dateto_c,

Row_d – datefrom_d – dateto_d,

Anybody got an idea how to do this?

Thanks for your help,

Nana

Re: select rows containing dateto and datefrom from table

Former Member — Tue, 28 Mar 2006 13:44:49 GMT

Hi nana,

1. PROVIDE

2. This statment is exactly for this purpose.

3. It will also return the SUBSET,

(in the sense what i understood)

4. Just copy paste this program,

and it will make things clear.

5. Just like yours,

it has an internal table

of dates, and it

BREAKS the information

and gives the output.

REPORT abc.

DATA: BEGIN OF itab3 OCCURS 0,

d1 TYPE sy-datum,

d2 TYPE sy-datum,

s TYPE string,

END OF itab3 VALID BETWEEN d1 AND d2.

DATA : itab4 LIKE TABLE OF itab3 WITH HEADER LINE.

DATA : mindate TYPE sy-datum.

DATA : maxdate TYPE sy-datum.

itab3-d1 = '20031229'.

itab3-d2 = '20031231'.

itab3-s = 'First'.

APPEND itab3.

itab3-d1 = '20040101'.

itab3-d2 = '20040129'.

itab3-s = 'Second'.

APPEND itab3.

itab3-d1 = '20031229'.

itab3-d2 = '20040129'.

itab3-s = 'Third'.

APPEND itab3.

itab3-d1 = '20040105'.

itab3-d2 = '20040110'.

itab3-s = 'Fourth'.

APPEND itab3.

SORT itab3 BY d2.

*----

get min max dates.

MINDATE = SY-DATUM.

LOOP AT itab3.

IF itab3-d1 < mindate.

mindate = itab3-d1.

ENDIF.

IF itab3-d2 < mindate.

mindate = itab3-d2.

ENDIF.

IF itab3-d1 > maxdate.

maxdate = itab3-d1.

ENDIF.

IF itab3-d2 > maxdate.

maxdate = itab3-d2.

ENDIF.

ENDLOOP.

*----

ITAB4

itab4-d1 = mindate.

itab4-d2 = maxdate.

itab4-s = 'AAAAAA'.

APPEND itab4.

*----

PROVIDE * FROM itab3 * FROM itab4

*BETWEEN '20031229' AND '20040129'.

BETWEEN mindate AND maxdate.

WRITE 😕 itab3-d1 , ' - ' , itab3-d2 , ' - ' , itab3-s.

ENDPROVIDE.

regards,

amit m.

Re: select rows containing dateto and datefrom from table

Former Member — Tue, 28 Mar 2006 13:45:35 GMT

Hi Nana,

In the graphic example it looks like all records (a,b,c, and d) have a date-from and date-to within the X-daterange, although you state the selection should only give b, c and d.

Am I missing something?

Regards,

John.

Re: select rows containing dateto and datefrom from table

rahulab — Tue, 28 Mar 2006 13:52:26 GMT

try this.

parameters: date like sy-datum.

select * from <table>

into table <itab>

where datefrom LT date AND dateto GT date.

regards.

rahul.