Using DAYS_BETWEEN in a Case When statement

Hi all,
I want to set a value based on different conditions. If I do

Median(
case when “Eventlog_casestates_GDP_csv”.“CASE_STATE” = ‘running’
then
case when (<%= Durchlaufzeit_Median %> > DAYS_BETWEEN(PU_FIRST(“Eventlog_cases_GDP_csv”, “Eventlog_activities_GDP_csv”.“TIMESTAMP”),TODAY()))
then <%= Durchlaufzeit_Median %>
else NULL
end
else CALC_THROUGHPUT(ALL_OCCURRENCE[‘Process Start’] TO ALL_OCCURRENCE[‘Process End’], REMAP_TIMESTAMPS(“Eventlog_activities_GDP_csv”.“TIMESTAMP”, DAYS))
end)

but actually I want to set the days between as well. So if I do

Median(
case when “Eventlog_casestates_GDP_csv”.“CASE_STATE” = ‘running’
then
case when (<%= Durchlaufzeit_Median %> > DAYS_BETWEEN(PU_FIRST(“Eventlog_cases_GDP_csv”, “Eventlog_activities_GDP_csv”.“TIMESTAMP”),TODAY()))
then <%= Durchlaufzeit_Median %>
else DAYS_BETWEEN(PU_FIRST(“Eventlog_cases_GDP_csv”, “Eventlog_activities_GDP_csv”.“TIMESTAMP”),TODAY())
end
else CALC_THROUGHPUT(ALL_OCCURRENCE[‘Process Start’] TO ALL_OCCURRENCE[‘Process End’], REMAP_TIMESTAMPS(“Eventlog_activities_GDP_csv”.“TIMESTAMP”, DAYS))
end)

this error occurs: “Operator requirements are not met. Operator ‘CASE’ is not compatible with cases of type: INT and FLOAT.” How can I solve this?

Hi,

in a CASE WHEN, you always need to return values of the same data type.
DAYS_BETWEEN returns a FLOAT, while CALC_THROUGHPUT returns an INT. As the first query works, I assume that the Durchlaufszeit_Median variable is an INT as well?
So you have two options: You can either apply ROUND or FLOOR or CEIL to the DAYS_BETWEEN to make it an INT, or you can multiply the CALC_THROUGHPUT and the Durchlaufszeit_Median variable with 1.0, which basically casts the INT to a FLOAT.

Cheers
David

1 Like

Hallo David,
thank you so much! Using “round” works perfectly.
Kr, Mara