Hi,
I have report requirement that we need to pull out all employees who have over 20 leave days consecutively, so application support team can reactivate some applications if their account is suspended during holiday period.
Some useful tables columns are:
employee_id, leave_type, absence_from_date, absence_to_date, absence_date. etc.
absence_from_date to absence_to_date is leave dates range (work days only). absence_date column lists each day of the leave (excludes weekends and public holidays). If a single leave request is over 20 days, that's fairly simple to count. Just partition by employee id, count(absence_date), if over 20, get the employee. However, the difficulty is when they submit multiple leaves, how do we detect they are consecutive leaves? It sounds simple request, but it's pretty hard to code it using SQL.
Cheers
Victor