Here's one approach you could try.
Create a lookup of the annual leave record (say annual_leave.csv). This csv should have 3 columns Staff Day OnLeave (change the format of your current list). Preferably, this should only contain Day when Staff is on leave. (John 1_Sep AL)
Then, run this query for your final report
... | eval dm=strftime(_time, "%-d_%b") | lookup annual_leave.csv Staff AS "Staff name" Day AS dm OUTPUT OnLeave | eval intime=strptime(x, "%-H:%M") | eval starttime=strptime("9:00", "%-H:%M")| eval ontime=case(OnLeave="AL", "N/A", isnull(OnLeave) AND intime>starttime, "No", intime<starttime, "Yes", 1=1, "UNK") | chart values(ontime) as ontime over "Staff name" by dm
Assumptions:
_time should be time from daily in-time record, representing each day of the month
dm format should match format of values in Day field in leave csv.
OnLeave column only has "AL" representing "Leave"
... View more