Continous date in data range in sql server

Question

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

select employee,attendancedate,

(select (case when count(employee) >= 3 then 1 else 0 end)
  from att_tblextattendance
where employee = a.employee and session1status=5 and session2status=5
and attendancedate between dateadd(day,-3,'2014-05-30 00:00:00.000') and '2014-05-30 00:00:00.000') as bCount,

 (select (case when count(employee) >= 3 then 1 else 0 end)
 from att_tblextattendance
 where employee = a.employee and session1status=5 and session2status=5
 and attendancedate between '2014-05-30 00:00:00.000' and dateadd(day,3,'2014-05-30 00:00:00.000')) as fCount

from att_tblextattendance a where employee=498 and (session1status=5 or session2status=5)

Here if employee absent continuous 3 days then bcount or fcount should be 1 else 0,but problem here is above select date range if employee is not absent continuous then also bcount is updating as 1. above query result. rows are employee, attendancedate,bcount and 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 is --

  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 some suggestion how should i check is date are continuous in date range.

Your question is not clear yet. Explain what date column stores, also how the 2 boolean column values are calculated clearly. — Sateesh Pagolu, Aug 17 '15 at 05:41
You might find a similar problem (and solution) here: [how-to-find-consecutive-rows-based-on-the-value-of-a-column](http://stackoverflow.com/questions/18436749/how-to-find-consecutive-rows-based-on-the-value-of-a-column) — Carsten Massmann, Aug 17 '15 at 05:42

score 1 · Accepted Answer · edited Jan 18 '21 at 12:32

You could try the following:

WITH cnts AS (
  SELECT att-row_number() OVER (PARTITION BY emp ORDER BY att) diff,
  * from #tbl  
), grp AS ( 
  SELECT emp em,count(*) cnt,min(att) att1, max(att) att2 
  FROM cnts GROUP BY emp,diff )
SELECT emp,att,CASE WHEN cnt>2 THEN 1 ELSE 0 END bcnt, fcnt 
FROM #tbl t INNER JOIN grp ON em=emp AND att between att1 and att2
ORDER BY att

See here for a live demo.

This will give you a list for all employee-ids in one go. The basic trick here is that I do a group by over the difference diff between an attendance date and the row_number() per employee. If the difference stays the same then these rows are rows with consecutive dates. I am not sure, what you want showing in column fcnt, so this columns is left unchanged (0) in my example.

Continous date in data range in sql server

1 Answers1