Today I got a requirement to display stock aging data for any previous period and I decided to use table CKMLHD&CKMLPP. But I then got a problem on selecting data from the large table CKMLPP. In this table we have some data like belows:
KALNR BDATJ POPER LBKUM 100515932 2009 012 22.000 100515932 2010 001 12.000 100931044 2010 001 1.000 100931044 2010 002 1.000 ...
Now we need to get stock data (field CKMLPP-LBKUM) from the latest period(CKMLPP-BDATJ + CKMLPP-POPER). For here we want to get the following records:
KALNR BDATJ POPER LBKUM 100515932 2010 001 12.000 100931044 2010 002 1.000 ...
Considering the table size (more than 4 million of lines), we don't want to select all of them into internal tables and then compare each other to get the latest data. I tends to use some aggregate expressions like max( CKMLPP-BDATJ + CKMLPP-POPER ) to minimize the workload, but unfortunately, the expression max() does not support column combination, it can only select max( CKMLPP-BDATJ ) or max( CKMLPP-POPER ), but that's meanless.
I know normally for material ledge records, it should have stock data in one line per period in CKMLPP even there is no GR/GI during the specified period, but for some reason, lots of period data not exist so we can not just use sql like select LBKUM from CKMLPP where BDATJ = p_period0(4) and POPER = p_period4(3).
So could any of you give me an idea how to handle this situation efficiently?
Thank you very much!