Skip to Content
author's profile photo Former Member
Former Member

getting next maximum values from database table?

Dear Experts,

Internal table date will be like this

Material No. Date Quantity

10001010 28.08.2013 10

10001810 28.08.2012 12

based on this internal table data i need to select next maximum date based on material no from mseg

example:

if data base like this

Material No. Date Quantity

10001010 28.08.2013 10

10001010 20.07.2013 5

10001010 10.04.2013 2

10001810 28.08.2012 12

10001810 21.08.2012 1
10001810 2.07.2012 21

i want data like this

Material No. Date Quantity

10001010 20.07.2013 5

10001810 21.08.2012 1

How we can get this values using select statement

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

9 Answers

  • Best Answer
    Posted on Sep 30, 2013 at 02:59 AM

    Hi Ganesh,

    You try like this

    types : begin of ty_mkpf_mseg, " MKPF - Header: Material Document & MSEG Document Segment: Material
    mblnr type mkpf-mblnr, " Material doc. no
    mjahr type mkpf-mjahr, " Material Document Year
    budat type mkpf-budat, " Posting Date in the Document
    cpudt type mkpf-cpudt, " Day On Which Accounting Document Was Entered
    zeile type mseg-zeile, " Mat. Doc. Item no.
    bwart type mseg-bwart, " Movement type
    matnr type mseg-matnr, " Material No.
    werks type mseg-werks, " Plant
    shkzg type mseg-shkzg, " Amount Indicator
    dmbtr type mseg-dmbtr, " Amount
    menge type mseg-menge, " Quantity
    meins type mseg-meins, " Unit of measure
    aufnr type mseg-aufnr, " Order No.
    kdauf type mseg-kdauf, " Sales Order Number
    end of ty_mkpf_mseg,

    Data : it_mkpf type table of ty_mkpf_mseg,
    it_mkpf_pp type table of ty_mkpf_mseg,

    selection-screen : begin of block b1 with frame title text-001.
    parameters : p_date type sy-datum obligatory.
    selection-screen : end of block b1.

    select a~mblnr
    a~mjahr
    a~budat
    a~cpudt
    b~zeile
    b~bwart
    b~matnr
    b~werks
    b~shkzg
    b~dmbtr
    b~menge
    b~meins
    b~aufnr
    b~kdauf
    from mkpf as a inner join mseg as b
    on a~mblnr eq b~mblnr and a~mjahr eq b~mjahr
    into table it_mkpf
    where a~cpudt = p_date.


    select a1~mblnr
    a1~mjahr
    a1~budat
    a1~cpudt
    b1~zeile
    b1~bwart
    b1~matnr
    b1~werks
    b1~shkzg
    b1~dmbtr
    b1~menge
    b1~meins
    b1~aufnr
    b1~kdauf
    from mkpf as a1 inner join mseg as b1
    on a1~mblnr eq b1~mblnr and a1~mjahr eq b1~mjahr
    into table it_mkpf_pp
    for all entries in it_mkpf
    where matnr = it_mkpf-matnr
    and a1~cpudt = ( select max( c~cpudt )
    from mkpf as c inner join mseg as d
    on c~mblnr eq d~mblnr and c~mjahr eq d~mjahr
    where d~matnr = it_mkpf-matnr
    and c~cpudt lt it_mkpf-cpudt).

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 29, 2013 at 07:05 AM

    I have a feeling that this can be done using subquery and aggregate functions.

    What "Date" field in MSEG are you referring to? Most relevant date fields are in header table MKPF.

    Thomas

    Add a comment
    10|10000 characters needed characters exceeded

    • You are right sub-queries can do the job (But Beware on performance)

      Sample:

      REPORT z_nextrecord.

      TYPES :
      BEGIN OF l_mseg,
      matnr TYPE mseg-matnr,
      cpudt TYPE mseg-cpudt_mkpf,
      menge TYPE mseg-menge,
      END OF l_mseg.

      DATA: gt_keys TYPE STANDARD TABLE OF l_mseg,
      gt_mseg TYPE STANDARD TABLE OF l_mseg.


      SELECT matnr cpudt_mkpf menge
      INTO TABLE gt_keys
      FROM mseg UP TO 10 ROWS.
      SORT gt_keys BY matnr.
      DELETE ADJACENT DUPLICATES FROM gt_keys COMPARING matnr.

      CHECK gt_keys[] IS NOT INITIAL.

      SELECT matnr cpudt_mkpf menge
      INTO TABLE gt_mseg
      FROM mseg
      FOR ALL ENTRIES IN gt_keys
      WHERE matnr = gt_keys-matnr
      AND cpudt_mkpf = ( SELECT MIN( cpudt_mkpf ) FROM mseg
      WHERE matnr = gt_keys-matnr AND cpudt_mkpf GT gt_keys-cpudt ) .

      SORT gt_mseg BY matnr.

      BREAK-POINT.

      NB: I used fields from MKPF copied into MSEG as of Note 1516684 - MKPF fields added to MSEG - Performance optimization, else JOIN between MSEG and MKPF would be required... (Fields CPUDT and BUDAT were copied into MSEG)

      Regards,

      Raymond

  • Posted on Sep 30, 2013 at 06:15 AM

    I thought I was the only one thinking subquery to solve it. Raymond's solution looks good while Ramesh's seems quite odd with join, FAE and subquery in the same statement.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 02:09 PM

    Hi,

    first extract your data, and in second step find the good one.

    The big loose of time in ABAP is the SELECT statement, so always perform simple and faster SELECT statement.

    After you could make a SORT and a READ on the internal table.

    MSEG is one of the biggest table of SAP, don't try to make SORT, MAX, GROUP BY ... on it

    regards

    Fred

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 02:33 PM

    Hello

    Select everything in the correct filters, then use the loop on the internal table, but first sort the data by date and material.

    Inside the loop you can use logic to store the date and the material into variables and compare with the next record in loop to check the maximum date.

    Please read about Sort:

    http://help.sap.com/saphelp_470/helpdata/pt/fc/eb3800358411d1829f0000e829fbfe/content.htm

    And read about at new in loop.

    http://help.sap.com/saphelp_470/helpdata//EN/fc/eb381a358411d1829f0000e829fbfe/content.htm

    LOOP AT <itab>.
    AT FIRST. ... ENDAT.
    AT NEW <f1>. ...... ENDAT.
    AT NEW <f
    2 >. ...... ENDAT.
    .......
    <single line processing>
    .......
    AT END OF <f2>. ... ENDAT.
    AT END OF <f1>. ... ENDAT.
    AT LAST. .... ENDAT.
    ENDLOOP.

    The innerm

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 28, 2013 at 02:34 PM

    Hi,

    Try with below method

    REPORT ZGET_SECOND_VALUE.

    TYPES : BEGIN OF TP_MSEG,
    MATNR TYPE MATNR,
    DATE TYPE SY-DATUM,
    QTY TYPE MENGE_D,
    END OF TP_MSEG,

    BEGIN OF TP_MAKT,
    MATNR TYPE MATNR,
    END OF TP_MAKT.

    DATA : GT_MSEG TYPE STANDARD TABLE OF TP_MSEG,
    GS_MSEG TYPE TP_MSEG,
    GT_MSEG1 TYPE STANDARD TABLE OF TP_MSEG,
    GS_MSEG1 TYPE TP_MSEG,
    GT_MAKT TYPE STANDARD TABLE OF TP_MAKT,
    GS_MAKT TYPE TP_MAKT.

    DATA : GV_CNT TYPE I.


    " select data from mseg into gt_mseg .

    " select matnr from makt for all entries in gt_mseg




    LOOP AT GT_MAKT INTO GS_MAKT.

    CLEAR : GV_CNT.

    LOOP AT GT_MSEG INTO GS_MSEG WHERE MATNR = GS_MSEG-MATNR.
    GV_CNT = GV_CNT + 1.

    IF GV_CNT = 3.
    EXIT.
    ELSE.
    APPEND GS_MSEG TO GT_MSEG1.
    ENDIF.

    CLEAR : GS_MSEG, GS_MSEG1.
    ENDLOOP.

    CLEAR : GS_MAKT.
    ENDLOOP.


    SORT GT_MSEG1 BY MATNR ASCENDING DATE DESCENDING.
    DELETE ADJACENT DUPLICATES FROM GT_MSEG1 COMPARING MATNR.

    Regards,

    Zafar

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 29, 2013 at 02:53 AM

    Hi Ganesh,

    I am gonna use 3 internal tables for your program with same structure.

    select statement and values into it_table1.

    it_table1 have the value (sorted by material Number ascending and date descending):

    Material No. Date Quantity

    10001010 28.08.2013 10

    10001010 20.07.2013 5

    10001010 10.04.2013 2

    10001810 28.08.2012 12

    10001810 21.08.2012 1
    10001810 2.07.2012 21


    it_table2[] = it_table1[].

    In this case it_table2 has same as it_table1.

    delete adjacent duplicates from it_table2 comparing material number.

    IT_TABLE2:

    Material No. Date Quantity


    10001010 28.08.2013 10
    10001810 28.08.2012 12


    Loop at it_table2 into wa_table2.

    read it_table1 into wa_table1 with key material number eq material number date ne wa_table2-date.

    if sy-subrc eq 0.

    append wa_table1 to it_table3.

    endif.

    ENdloop.

    Free: it_table2.

    IT_TABLE3:

    Material No. Date Quantity


    10001010 20.07.2013 5
    10001810 21.08.2012 1

    So you have it_table1 with all values and it_table3 with second values. if you need you can use it_table2 for 1st values in your program for future validations.

    Note: if there is no second record for a material number then it will be skipped.

    With Regards,

    Giriesh M

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 29, 2013 at 04:45 AM

    Hello Ganesh .

    plaese mention what is the condition you want to find the data in internal table.

    Material No. Date Quantity

    10001010 28.08.2013 10

    10001010 20.07.2013 5

    10001010 10.04.2013 2

    10001810 28.08.2012 12

    10001810 21.08.2012 1
    10001810 2.07.2012 21

    i want data like this

    Material No. Date Quantity

    10001010 20.07.2013 5

    10001810 21.08.2012 1

    from 10001810 material you want

    10001810 21.08.2012 1
    from 10001010 material you want
    10001010 20.07.2013 5

    even though other post but unable get the condition where as
    10001010 material qty 2 is their why you want 5?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 29, 2013 at 07:38 AM

    HI,

    i would select only the entries up to a specific date. I think for you it should be sy-datum.

    Then you can easily sort by date matnr.

    BR

    Robert

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.