Any help with this is appreciated. I am trying to label any records on Friday, Saturday and Sunday all day and Monday before 12:01PM "On time" and anything older than that "Late" or later than Monday 12:01PM "late". This is the logic I've used so far, but I still can't seem to find a way to get it to work 100%
IF(TEST_RESULT = '30'
AND Interval(Now() - RESULT_DATE,'D') >= 6, 'Late',
IF(TEST_RESULT = '30'
AND (num(weekday( [RESULT_DATE], 6 ))) = 1
ANDTime(RESULT_DATE, 'hh:mm:ss') < '12:01:00 PM'
AND Interval(Now() - RESULT_DATE,'D') < 6, 'On time',
IF(TEST_RESULT = '30'
AND (num(weekday( [RESULT_DATE], 6 ))) = 1
ANDTime(RESULT_DATE, 'hh:mm:ss') > '12:01:00 PM'
AND Interval(Now() - RESULT_DATE,'D') < 4, 'Late',
IF(TEST_RESULT = '30'
AND (num(weekday( [RESULT_DATE], 6 ))) = 2
OR (num(weekday( [RESULT_DATE], 6 ))) = 3
OR (num(weekday( [RESULT_DATE], 6 ))) = 4
AND Interval(Now() - RESULT_DATE,'D') <= 6, 'Late',
IF(TEST_RESULT = '30'
AND (num(weekday( [RESULT_DATE], 6 ))) = 5
OR (num(weekday( [RESULT_DATE], 6 ))) = 6
OR (num(weekday( [RESULT_DATE], 6 ))) = 0
AND Interval(Now() - RESULT_DATE,'D') < 5, 'On time',
))))) AS [Status],
Attached picture has the columns used ([RESULT_DATE], [STATUS],[DAYS SINCE LAST TEST])
I think the AND and OR within the last two if-loops aren't correct defined - they need be appropriate wrapped with brackets to ensure the right order of execution.
Beside this an extra wrapping of a function doesn't improved the readability and the if-query to TEST_RESULT is redundant and could shortened. Further such a construct could be simplified by applying a multi-stage approach within a preceeding-load, means something like:
load *, if(match(X, 5, 6), 'x', 'y') as Y; load *, num(weekday(RESULT_DATE)) as X from Y;
