sql - how to find employee who is absent continuously more than 10 days? -

sql - how to find employee who is absent continuously more than 10 days? -

i have table name att. has 2 columns: empid , date. date column contains dates employee present. need write query find if employee continuously absent more 10 days.

empid | date 101 | 1/1/2012 101 | 2/1/2012 101 | 7/1/2012 101 | 18/1/2012 101 | 21/1/2012 101 | 25/1/2012 101 | 30/1/2012 102 | 1/1/2012 102 | 2/1/2012 102 | 5/1/2012 102 | 9/1/2012 102 | 14/1/2012 102 | 19/1/2012 102 | 24/1/2012 102 | 25/1/2012 102 | 28/1/2012 102 | 29/1/2012 102 | 30/1/2012

the result should 101 here. how can done? please help.

if working sql server 2012, can utilize lead analytical function

with recordlist ( select empid, date fromdate, lead(date) on (partition empid order date asc) todate tablename ) select distinct empid recordlist datediff(d, fromdate ,todate) >= 10 sqlfiddle demo sqlfiddle demo (others)

other link(s)

datediff lead

update 1

with firstlist ( select empid, date, row_number() on (partition empid order date asc) rn tablename ) select distinct a.empid firstlist inner bring together firstlist b on a.rn + 1 = b.rn datediff (d, a.date , b.date ) >= 10 sqlfiddle demo

sql sql-server tsql sql-server-2005