Extreme values within each group of dataset

Question

I have an SQLScript query written in AMDP which creates two new columns source_contract and target_contract.

RETURN SELECT client as client,
    pob_id as pob_id,
    dateto as change_to,
    datefrom as change_from,
    cast( cast( substring( cast( datefrom as char( 8 ) ), 1,4 ) as NUMBER ( 4 ) ) as INT )
        as change_year,
    cast( CONCAT( '0' , ( substring( cast( datefrom as char( 8 ) ), 5,2  ) ) ) as VARCHAR (3))
        as change_period,
    LAG( contract_id, 1, '00000000000000' ) OVER ( PARTITION BY pob_id ORDER BY pob_id, datefrom )
        as source_contract,
    contract_id as target_contract
    from  farr_d_pob_his
    ORDER BY pob_id

Original data:

POB     Valid To    Valid From  Contract
257147  05.04.2018  05.04.2018  10002718
257147  29.05.2018  06.04.2018  10002719
257147  31.12.9999  30.05.2018  10002239

Data from AMDP view:

I want to ignore any intermediate rows (Date is the criteria to decide order). Any suggestion or ideas ?

I thought of using Group by to get the max date and min date and using union on these entries in a separate consumption view but if we are using group by we can't fetch other entries. The other possibility is order by date but it is not available in CDS.

What are intermediate entries? Show them on your sample data — Suncatcher, Oct 04 '19 at 00:07
Data is already in the question and let me share the context. Contracts get created every time but we only care about the first contract and the last contract. There might be 100 contracts but only first and last are important. I have written a Union with Inner Selects on max and min dates for now but would like to know other approaches. — Pavan Kumar Polavarapu, Oct 04 '19 at 01:51

score 0 · Accepted Answer · answered Oct 04 '19 at 09:31

You already have the optimal solution with sub-selects.

Pseudo code:

SELECT *
  FROM OriginalData
  WHERE (POB, ValidFrom)
     IN (SELECT POB, MIN(ValidFrom)
          FROM OriginalData
          GROUP BY POB)
    OR (POB, ValidTo)
     IN (SELECT POB, MAX(ValidTo)
           FROM OriginalData
           GROUP BY POB);

GROUP BY won't work as it "mixes up" the minimums in different columns.

A nice touch might be extracting the sub-selects into views of their own, eg. EarliestContractPerPob and LatestContractPerPob.

score 0 · Answer 2 · answered Oct 27 '19 at 01:41

Here is the proof-of-concept of solution for your task.

Provided we have pre-selected by material type (MTART) dataset based on table mara which is quite similar to yours:

------------------------------------------------
|        MATNR     |   ERSDA  |   VPSTA  |MTART|
------------------------------------------------
|       17000000007|18.06.2018|KEDBXCZ   |ZSHD |
|       17000000008|21.06.2018|K         |ZSHD |
|       17000000011|21.06.2018|K         |ZSHD |
|       17000000023|22.06.2018|KEDCBGXZLV|ZSHD |  
|       17000000103|09.01.2019|K         |ZSHD |
|       17000000104|09.01.2019|K         |ZSHD |
|       17000000105|09.01.2019|K         |ZSHD |
|       17000000113|06.02.2019|V         |ZSHD |
------------------------------------------------

Here are the materials and we want to leave only the last and the first material (MATNR) by creation date (ERSDA) and find maintenance type (VPSTA) for first and last ones.

------------------------------------------------
|        MATNR     |   ERSDA  |   VPSTA  |MTART|
------------------------------------------------
|       17000000007|18.06.2018|KEDBXCZ   |ZSHD |
|       17000000113|06.02.2019|V         |ZSHD |
------------------------------------------------

In your case you similarly search within each POB (mtart) source and target contracts contract_id (last and first vpsta) on the basis of datefrom criterion (ersda).

One can achieve that using UNION and two selects with sub-queries:

 SELECT ersda AS date, matnr AS max, mtart AS type, vpsta AS maint
   FROM mara AS m
  WHERE ersda = ( SELECT MAX( ersda ) FROM mara WHERE mtart = m~mtart )
    UNION SELECT ersda AS date, matnr AS max, mtart AS type, vpsta AS maint
     FROM mara AS m2
    WHERE ersda = ( SELECT MIN( ersda ) FROM mara WHERE mtart = m2~mtart )
    ORDER BY type, date
     INTO TABLE @DATA(lt_result).

Here you can notice the first select fetches max ersda dates and the second select fetches min ones.

The resulted set ordered by type and date will be somewhat what are you looking for (F = first, L = last):

Your SELECT should look somewhat like this:

 SELECT datefrom as change_from, contract_id AS contract, pob_id AS pob
   FROM farr_d_pob_his AS farr
  WHERE datefrom = ( SELECT MAX( datefrom ) FROM farr_d_pob_his WHERE pob_id = farr~pob_id )
    UNION SELECT datefrom as change_from, contract_id AS contract, pob_id AS pob
     FROM farr_d_pob_his AS farr2
    WHERE datefrom = ( SELECT MIN( datefrom ) FROM farr_d_pob_his WHERE pob_id = farr2~pob_id )
    ORDER BY pob, date
     INTO TABLE @DATA(lt_result).

Note, this will work only if you have unique datefrom dates, otherwise the query will not know which last/first contract you want to use. Also, in case of the only one contract within each POB there will be only one record.

A couple of words about implementation. In your sample I see that you use AMDP class but later you mentioned that ORDER is not supported by CDS. Yes, they are not supported in CDS as well as sub-queries, but they are supported in AMDP.

You should differentiate two types of AMDP functions: functions for AMDP method and functions for CDS table functions. The first ones perfectly handle SELECTs with sorting and sub-queries. You can view the samples in CL_DEMO_AMDP_VS_OPEN_SQL demo class which demonstrate AMDP features including sub-queries. You can derive you code in AMDP function and call it from your CDS table function implementation.

Extreme values within each group of dataset

2 Answers2