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.

Answer 1

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.