how to make a MAX() ?

[Former Member]

Hello! Have You got some idea how to change the code below to be correct?

REPORT ZWOP_TEST1 .

DATA :
  people TYPE STANDARD TABLE OF zpersonal.

SELECT nachn 
INTO TABLE people
FROM zpersonal
INNER JOIN zintagral 
ON zintagral~card = zpersonal~stredisko_aktual
WHERE zintagral~fili = '1200' 
AND   zintagral~rdate = SY-DATUM
AND   MAX(zintagral~pohyb) = 'I'.              ' an error

I want to check who is present at work at this moment. In table <b>zintagral</b> there are information about logging in and out from work. The field <b>rtime</b> keeps information about all logging actions (log in/log out). The field <b>pohyb</b> keeps information about type of logging action ( 'I' - log in; 'O' - log out ).

I would like to put the names of people their MAX rdate is the type of 'I', because theoretically they should be present at work at this moment.

Greetings

Message was edited by:

Piotr Wojciechowski

[Former Member]

Hi,

In the above code ur selecting a single column use "INTO CORRESPONDING FIEDLS OF TABLE PEOPLE".

When ever u use MAX function for a column u ll get only one record at a time.

Thanks & Regards

Santhosh

[dev_parbutteea]

SELECT nachn

INTO TABLE people

FROM zpersonal

INNER JOIN zintagral

ON zintagral_{card = zpersonal}stredisko_aktual

WHERE zintagral~fili = '1200'

AND zintagral~rdate = SY-DATUM

<b>AND MAX(zintagral~rtime) = 'I'. ' an error</b>

i think that it shoud be :

AND MAX(zintagral~pohyb ) = 'I'.

because rtime contains time i suppose

Regards,

Sooness

[Former Member]

To: Sooness Munogee

Of course, You wright. My mistake, I've corrected it.

Message was edited by:

Piotr Wojciechowski

[dev_parbutteea]

HI,

you should close this thread and reward all helpful answers!

Regards,

Sooness

[Former Member]

Hi Piotr.

You use MAX() only to get the highest value for a field with otherwise specific database key (kind of maximum free seats in a list of planes).

In your case you should try to use following.

REPORT ZWOP_TEST1.

DATA: people     TYPE STANDARD TABLE OF zpersonal,
          cards       TYPE STANDARD TABLE OF zintagral,
          logcards   TYPE STANDARD TABLE OF zintagral,
          s_card     TYPE zintagral.

* All Log-Entries from actual date
SELECT card FROM zintagral INTO TABLE cards
  WHERE fili = '1200'
      AND  rdate = SY-DATUM.

* Sort by card and rtime (so newest is on top)
SORT cards BY card rtime DESCENDING.

* Loop over entries and add first one, when login.
LOOP AT cards INTO s_card.
  AT NEW card.
    IF s_card-pohyb = 'I'.
      ADD s_card TO logcard.
    ENDIF.
  ENDAT.
ENDLOOP.

* Select last names from personal table
SELECT nachn INTO TABLE people
  FROM zpersonal
  FOR ALL ENTRIES IN logcards
  WHERE stredisko_aktual = logcards-card.

Regards,

Timo

[Former Member]

Thanks for help