I am trying to screen the cases where we set order block and immediately release without any activities in the middle. The idea is to calcuate how much time we wasted on those cases.
To do that: In the activities table, I want to partition along column "Change_to" in such a way:
When "Change_from" = 'ZA' (which incdicates release of block)
Then look at the immediate preceding rows: if the column "Change_to" = 'ZA' (indicate set block)
Then calculate the datediff between those rows.
This is my code:
SUM(
CASE WHEN ("_CEL_O2C_ACTIVITIES"."CHANGED_FROM" = 'ZA' AND ACTIVITY_LAG ("_CEL_O2C_ACTIVITIES"."CHANGED_TO", 1) = 'ZA')
THEN
DATEDIFF ( MI, ACTIVITY_LAG("_CEL_O2C_ACTIVITIES"."EVENTTIME"),"_CEL_O2C_ACTIVITIES"."EVENTTIME")
END)
It seems that the above code just ignore the bolded part in the casewhen statement. It calculates the datediff even when the direct preceding row does not have "Change to" = 'ZA'.
Any ideas whats wrong with the code?