Find busiest CarrierId based on FlightDate using SAP CDS View

Hi Experts ,
I need to get the below expected output. Let me explain: as 07.04 CarrierId has highest number of trip that is 4 so it should be the output instead of 10.02. All other entries have single entry based on date so it will be in output.

Input data:

CarrierID Connection ID FlightDate Price

UA 1750 10.02.2024 250
UA 1890 07.04.2024 450
UA 2760 07.04.2024 150
UA 1970 07.04.2024 100
AA 3490 11.04.2024 340
AA 1960 13.04.2024 560
SQ 3490 15.05.2014 120


Expected output:

CarrierID FlightDate

UA 07.04.2024
AA 11.04.2024
AA 13.04.2024
SQ 15.05.2014

I tried to create two CDS Views to solve the issue but I can only get the busiest CarrierId without date in output. But requirement is to display the date with CarrierID.

I tried to find output with the below views:

  1. define view entity View1 as select from /dmo/booking  
     key carrier_id as CarrierId,
         flight_date as FlightDate,     
      count(*) as FlightCount   
    group by
  2. define view entity View2 as select from View1
     key CarrierId as CarrierId,
         FlightDate as FlightDate,
     max(FlightCount) as Numberofflight
    }group by CarrierId

    This view is not working as we need to add non-aggregated fields in the Group By clause but if I add FlightDate in the Group By then the output will be different.
Scroll to Top