Hi,
Im stuck with a tricky scenario.
I have a table which stores availablity data. It has employee id, slot begin time, scheduled slot length, availability status . availability status ='N' would indicate the employee is not available for that slot. So i have been asked to find how much time each employee were available based on the scheduled slots. So the user requirement is to calculate the total of scheduled slots (which would be sum(slot_length) ) minus unavailable time where the employee was unavailable for 30 mins or more
Table looks like this
Now heres the trick. when they say employee unavailable for more than 30 mins in the above case from 12.30-12.50 employee was unavailable(for three consecutive slots) and that totals to 30 mins. then again 16:10-16:20 employee was unavailable for 20 mins and 18:10-18:20 employee was unavailable for 20 mins. Note that even though 16:10-18:20 appears as consecutive rows in table, while calculating unavailable time 16:10&16:20 slots needs to be summed up together as they are consecutive time. Same applies to 18:10-18:20 as well. So for that day total unavailable time is 30 min (from 12.30-12.50 slots) as the other ones are less than 30 mins. how can i achieve this calculation using sql. my brain is dead as i tried partitioning self join and everything.