Code Pushdown - CDS date interval slicing

[ivo_skolek]

Interval slicing is a task that developers need to deal with when working with overlapping records with validity periods - such as conditions in SAP. At single point in time multiple records can be valid (perhaps with different condition type). This blog will show you how to achieve this in CDS views without writing single line of ABAP.

Overlapping intervals

Such data is saved in SAP in database table as per picture below - lets call the table ZSLICE_DB  (it has 4 records - corresponding with picture above).

How the table looks like in dictionary just for reference:

I recommend you create the table and enter records as per above. And later when doing the CDS views try to check output of each CDS views. I will not go into very details of "why it is done like this". Try to understand and test it.

There will be in total 5 CDS views until we get the final result. If you know simpler way, please share!

When saying "slicing intervals" it means graphically something like this:

Sliced intervals

In this blog, we will look at developing CDS ZSLICE_SLICED that will apply slicing logic on records in table ZSLICE_DB and will give back output of "sliced intervals" as per graphic. Screenshot below is sorted by "start date" and "end date" and you can see that in specific intervals more than "one condition type" is valid - which corresponds to the situation when different condition types are overlapping.

Output of CDS after slicing

In general the idea is following

• Get all slicing Start Dates - these are actual Start Dates of condition records and all End Dates + 1 (a slice can start day after previous slice finishes)


• Get all slicing End Dates - these are actual End Dates of condition records and all Start Dates - 1 (a slice can end day before next slice starts)


• Generate "extra slices" - records using these Start Dates and End Dates


• Reduce number of records to keep only "shortest intervals" per combination Condition Type & Start Date

So, lets get into some DDL coding ;-).

1. Create simple CDS ZSLICE_START_DATES that gives back all "Start dates" - from actual records from database but also extra "start dates" which are "end date + 1".

@AbapCatalog.sqlViewName: 'ZSLICESTARTDATES'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Slicing interval - all start dates'

define view ZSLICE_START_DATES as select from zslice_db

{

    //actual start dates

    key datab as StartDate

} 

union select from zslice_db

{

    //start dates generated as end date + 1

    key DATS_ADD_DAYS(datbi,1,'UNCHANGED') as StartDate

 }

2. Create simple CDS ZSLICE_END_DATES that gives back all "End dates" - from actual records from database but also extra "end dates" which are "start date - 1".

@AbapCatalog.sqlViewName: 'ZSLICEENDDATES'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Slicing interval - all end dates'

define view ZSLICE_END_DATES as select from zslice_db 

{

    //actual end dates

    key datbi as EndDate

}

union select from zslice_db

{

    //end dates generated as Start Date - 1

    key DATS_ADD_DAYS(datab,-1,'UNCHANGED') as EndDate

}

3. Create CDS ZSLICE_SLICED_START that gives back records from table ZSLICE_DB + extra records: additional slices for all start dates from step 1

@AbapCatalog.sqlViewName: 'ZSLICESLICEDSTA'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Slicing interval - extra slices for start dates'

define view ZSLICE_SLICED_START as select from zslice_db

{



//actual original records from database

    key type as ConditionType,

    key datab as StartDate,

    key datbi as EndDate

}



union select from ZSLICE_START_DATES as ExtraStartDates inner join zslice_db as OrigRecords

on OrigRecords.datab <= ExtraStartDates.StartDate //condition period covers this start date

and OrigRecords.datbi >= ExtraStartDates.StartDate

{



//added extra slices - where start date is day before end date of other record

    key OrigRecords.type as ConditionType,

    key ExtraStartDates.StartDate as StartDate,

    key OrigRecords.datbi as EndDate



}

 

4. Create CDS ZSLICE_END_DATES that selects all end dates from CDS from previous step + extra records: additional slices for all end dates from step 2

@AbapCatalog.sqlViewName: 'ZSLICESLICEDEND'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Slicing interval - extra slices for end dates'

define view ZSLICE_SLICED_END as select from ZSLICE_SLICED_START

{

//records from CDS with extra slices based on extra start dates

    key ConditionType as ConditionType,

    key StartDate as StartDate,

    key EndDate as EndDate



}



union select from ZSLICE_END_DATES as ExtraEndDates inner join ZSLICE_SLICED_START as SlicedStartRecords

on SlicedStartRecords.StartDate <= ExtraEndDates.EndDate //condition period covers the end date

and SlicedStartRecords.EndDate >= ExtraEndDates.EndDate

 {



//adding even more slices with end dates as start date - 1 of other records

    key SlicedStartRecords.ConditionType as ConditionType,

    key SlicedStartRecords.StartDate as StartDate,

    key ExtraEndDates.EndDate as EndDate



}

This CDS already returns all required records, but actually it returns too many.

You will notice that for single "Condition Type" and "Start Date" it has records with multiple "End Dates". We need to keep for every combination of "Condition Type" and "Start Date" only record with lowest "End Date" => keep only the shortest, most atomic slice.

5. We create final CDS ZSLICE_SLICED that selects from CDS from step 4 and applies "GROUP BY" and MIN:

@AbapCatalog.sqlViewName: 'ZSLICESLICED'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Sliced intervals - final CDS'

define view ZSLICE_SLICED as select from ZSLICE_SLICED_END {

    //keeping only shortest slices

    key ConditionType,

    key StartDate,

    min(EndDate) as EndDate

} group by ConditionType, StartDate

Finally, this gives us the desired output of "atomic slices".

 

Optional Step: Possible extension of "slicing" is to pick for each interval only one condition type with "highest priority". It is now easy to do that with a logic similar to this:

@AbapCatalog.sqlViewName: 'ZSLICEPRIORIT'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'Interval slicing - top priorities'

define view ZSLICE_PRIORITIZED as select from ZSLICE_SLICED {

    key StartDate,

    key EndDate,

    max(ConditionType) as TopPrioConditionType

} group by StartDate, EndDate

 

Interval slicing is something that can be very useful in business logic and its not trivial to implement.

In times of ABAP Programming Model for Fiori or Restful ABAP Programming Model, it is always desired to push as much "reading" logic into CDS views as possible - hopefully this blog will help you to achieve interval slicing scenarios without single line of ABAP ;-).

I tried to test it a bit, but there is a chance you find scenario / combination of data for which the slicing doesn't work - if so, please let me know in comments.