CODEWITHSUNDEEP
sql-server
Basavaraj
Basavaraj

Reputation: 1111

Continous date in data range in sql server

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.

Upvotes: 0

Views: 91

Answers (1)

Carsten Massmann
Carsten Massmann

Reputation: 28196

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.

Upvotes: 1

Related Questions