SQL records only for 3 consecutive months

Question

I have table for emp. attendance sheet:

emp_No Absent_Date
-------------------
111     01/03/2012
111     05/05/2012
222     13/02/2012
222     01/03/2012
222     02/03/2012
222     29/04/2012
222     09/09/2012
333     15/05/2012
333     18/09/2012
333     19/09/2012

I need to return the rows like below:

emp_No Absent_Date
-------------------
222     13/02/2012
222     01/03/2012
222     02/03/2012
222     29/04/2012

because only emp no 222 having absent in 3 consecutive months.

Answer 1

What you are trying to do is to group the absences by consecutive months. Let me assume that you are using a reasonable database that supports the dense_rank() function and basic window functions.

The idea is to find months in sequence that have absences. Then, count the number of months in each sequence for each employee and keep the ones that have more than three months.

The query does this by converting the month to a month number -- 12 times the year plus the month. It then uses a simple observation. The month number minus a sequence of numbers is a constant, for consecutive months. Usually, I use row_number() for the sequence. Because you have duplicate absences in a month, I'm using dense_rank().

select emp_no, absent_date
from (select a.*,
             max(monthnum) over (partition by emp_no, groupnum) as lastmonth,
             min(monthnum) over (partition by emp_no, groupnum) as firstmonth
      from (select a.*,
                   monthnum - dense_rank() over (partition by emp_no order by monthnum) as groupnum
            from (select a.*,
                         year(a.absent_date)*12+month(a.absent_date) as monthnum
                  from Attendance a
                 ) a
           ) a
     ) a
where lastmonth - firstmonth >= 2

Finally, because you want the absent dates -- as opposed to just the employee numbers -- I find the first and last month using window functions and use their difference as a filter.

Answer 2

I guess the easiest is to do a self join of the table three times, each time adding 1 month to the date:

SELECT DISTINCT S1.emp_No
FROM attendance_sheet S1
JOIN attendance_sheet S2
ON S1.emp_No = S2.emp_No
AND Month(S1.Absent_Date + 1 MONTH) = Month(S2.Absent_Date)
AND Year(S1.Absent_Date + 1 MONTH) = Year(S2.Absent_Date)
JOIN attendance_sheet S3
ON S2.emp_No = S3.emp_No
AND Month(S2.Absent_Date + 1 MONTH) = Month(S3.Absent_Date)
AND Year(S2.Absent_Date + 1 MONTH) = Year(S3.Absent_Date)

This will give you all the unique emp_No's. Now to get the result you want, you'll have to do another join (respectively I'll use IN for easier reading):

SELECT * 
FROM attendance_sheet
WHERE emp_No IN (
  SELECT S1.emp_No
  FROM attendance_sheet S1
  JOIN attendance_sheet S2
  ON S1.emp_No = S2.emp_No
  AND Month(S1.Absent_Date + 1 MONTH) = Month(S2.Absent_Date)
  AND Year(S1.Absent_Date + 1 MONTH) = Year(S2.Absent_Date)
  JOIN attendance_sheet S3
  ON S2.emp_No = S3.emp_No
  AND Month(S2.Absent_Date + 1 MONTH) = Month(S3.Absent_Date)
  AND Year(S2.Absent_Date + 1 MONTH) = Year(S3.Absent_Date)
)

See the SQL Fiddle to try (I had to change the month adding syntax from standard SQL to MySQL).