Count daily tasks based on timestamp field?

I have an Issue while using COUNT with DISTINCT on TIMESTAMP field. Below is my CDS view please help. I want to put COUNT on ConfirmedDate field.

 @AbapCatalog.sqlViewName: 'ZXEWMIWT'
 @AbapCatalog.compiler.compareFilter: true
 @AbapCatalog.preserveKey: true
 @AccessControl.authorizationCheck: #CHECK
 @EndUserText.label: 'Interface View for Warehouse Task detail'
 @OData.publish: true
// I* type:ddls

define view ZXEWMI_WT as select from /scwm/ordim_c as ORDIM_C {
    key ORDIM_C.lgnum as WarehouseNo,
    @UI.selectionField: [{ position: 1 }]
    @UI.lineItem: [{ position : 1 }]
    ORDIM_C.processor as Processor,
    @UI.lineItem: [{ position : 2 }]
    //count(distinct ORDIM_C.confirmed_at) as sum_wt

    **tstmp_to_dats( ORDIM_C.confirmed_at,
    abap_system_timezone( $session.client,'NULL' ),
    $session.client,
    'NULL' ) as ConfirmedDate**

}where processor <> '' 
group by lgnum, processor, confirmed_at;

Solution

It appears like you have millisecond-accurate timestamps in the field ORDIM_C.confirmed_at, but you want to group the results by day.

Unfortunately GROUP BY only allows you to group by columns from the input table(s)/view(s). It can not be used on calculated columns.

But what you can do is first create a separate CDS view which gives you the fields of the table /scwm/ordim_c with the timestamp converted to a date and then query that view.

View 1:

define view Z_ORDIM_C_WITH_DAY as 
select from /scwm/ordim_c as ORDIM_C {
    key ORDIM_C.lgnum as WarehouseNo,
    ORDIM_C.processor as Processor,
    tstmp_to_dats( ORDIM_C.confirmed_at,
    abap_system_timezone( $session.client,'NULL' ),
    $session.client,
    'NULL' ) as ConfirmedDate    
}

View 2:

define view ZXEWMI_WT as 
select from Z_ORDIM_C_WITH_DAY {
    key WarehouseNo,
    @UI.selectionField: [{ position: 1 }]
    @UI.lineItem: [{ position : 1 }]
    Processor,
    @UI.lineItem: [{ position : 2 }]
    count( * ) as sum_wt
    ConfirmedDate    
}
where processor <> ''
group by WarehouseNo, Processor, ConfirmedDate;