Hi everyone. I am trying to build-up a series of "cases" that will identify if our shipment is either "Hit", "Miss", or "Risk" in that order. "N/A" if shipment does not satisfy any of the 3.
We have created a logic for each identifier. It is showing as validated but we are not getting the expected results with a lot going to "N/A"
I shared the current beastmode below. Is there anything wrong how I created the logic? Please let me know if further clarification is needed. Thank you so much.
--------------------
Case when`actual arrival at pod date`is not NULL
AND
(DATEDIFF(`actual departure from pod date`,`actual arrival at pod date`) < = `Port free time`)
THEN 'HIT'
when `actual arrival at pod date` is not NULL
AND
(DATEDIFF(`actual departure from pod date`,`actual arrival at pod date` ) > `Port free time`)
OR
`actual arrival at pod date` is not NULL
AND
(DATEDIFF(`current date`,`actual arrival at pod date` ) > `Port free time`)
THEN 'MISS'
when `actual arrival at pod date` is not Null
AND
`actual departure from pod date` is NULL
AND
(DATEDIFF(`current date`,`actual arrival at pod date`) > = 3)
AND
(DATEDIFF(`current date`,`actual arrival at pod date` ) < `Port free time`)
Then 'Risk'
Else 'N/A'
END
