SQLTeam.com | Weblogs | Forums

Using LAG/LEAD in one go and with conditions

Hi All

I have a task to analyze how many hours are spent on long term sickness.
Rules are that if a person has on average the last 10 working days been sick for more than 90% of the standard working hours then it is long term sickness (classified as 2)

That it self was not a problem, but the rule also says that any other days with sickness is also long term sickness until the person has had no days with registered sickness for at least 5 working days.

I have no problem setting the classifications using loops, but I was hoping to do it in one go.

Any suggestions ?

Thank you