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
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.
No comments:
Post a Comment