Continous date in data range in sql server -

i want show coloring if employee absent consecutive day,so writing query -

select employee,attendancedate,  (select (case when count(employee) >= 3 1 else 0 end)   att_tblextattendance employee = a.employee , session1status=5 , session2status=5 , attendancedate between dateadd(day,-3,'2014-05-30 00:00:00.000') , '2014-05-30 00:00:00.000') bcount,   (select (case when count(employee) >= 3 1 else 0 end)  att_tblextattendance  employee = a.employee , session1status=5 , session2status=5  , attendancedate between '2014-05-30 00:00:00.000' , dateadd(day,3,'2014-05-30 00:00:00.000')) fcount  att_tblextattendance employee=498 , (session1status=5 or session2status=5) 

here if employee absent continuous 3 days bcount or fcount should 1 else 0,but problem here above select date range if employee not absent continuous bcount updating 1. above query result. rows employee, attendancedate,bcount , fcount.

  498     2013-07-25 00:00:00.000   1       0   498     2013-07-26 00:00:00.000   1       0   498     2014-05-27 00:00:00.000   1       0   498     2014-05-29 00:00:00.000   1       0   498     2014-05-30 00:00:00.000   1       0 

expected output --

  498     2013-07-25 00:00:00.000   1       0   498     2013-07-26 00:00:00.000   1       0   498     2014-05-27 00:00:00.000   1       0   498     2014-05-29 00:00:00.000   0       0   498     2014-05-30 00:00:00.000   0       0 

please give me suggestion how should check date continuous in date range.

you try following:

with cnts (   select att-row_number() on (partition emp order att) diff,   * #tbl   ), grp (    select emp em,count(*) cnt,min(att) att1, max(att) att2    cnts group emp,diff ) select emp,att,case when cnt>2 1 else 0 end bcnt, fcnt  #tbl t inner join grp on em=emp , att between att1 , att2 order att 

see here live demo.

this give list employee-ids in 1 go. basic trick here group by on difference diff between attendance date , row_number() per employee. if difference stays same these rows rows consecutive dates. not sure, want showing in column fcnt, columns left unchanged (0) in example.