Continuous absent query

RA UL PATEL

I am creating report for displaying continuous absent for more than 2 days.Query is build and working fine.Range is passed by using @StartDate and @EndDate. but problem is if In between two absent week off comes then it is also considered as continuous absent.i want to add that functionality to the existing query.How can i do that?

Existing query is:

WITH Dates
(
EntryDate,
EmployeeNumber,
Status,
Days,
EmployeeCode,
EmployeeName,
DeptName,
JobName,
HOD,
Supervisor
)
AS
(
SELECT
a.[DATE],
a.EmployeeID,
a.Status,
1,a.EmployeeCode,a.EmployeeName,a.DeptName,a.JobName,a.HOD,a.Supervisor
FROM
tblEmployeeAttendance1 a

WHERE
    a.\[Date\] between @StartDate and @EndDate and (a.status='AB' OR a.Status='O')
-- RECURSIVE    
UNION ALL

SELECT
    a.\[DATE\],
    a.EmployeeID,
    a.Status,
    CASE WHEN (a.Status = Parent.Status) THEN Parent.Days + 1 ELSE 1 END,
    a.EmployeeCode,a.EmployeeName,a.DeptName,a.JobName,a.HOD,a.Supervisor
FROM
    tblEmployeeAttendance1 a 
INNER JOIN
    Dates parent
ON
    datediff(day, a.\[DATE\], DateAdd(day, 1, parent.EntryDate)) = 0 
AND
    a.EmployeeID = parent.EmployeeNumber  where a.\[Date\] between @StartDate and @EndDate and (a.status='AB' OR a.Status='O')

)
SELECT * FROM Dates where days>=2 order by EmployeeNumber, EntryDate

Corporal Agarn

You may need to build a calendar of work days. Saw a link a couple of days ago but do not remember where. :doh:

Hitesh R

WITH Dates
(
EntryDate,
EmployeeNumber,
Status,
Days,
EmployeeCode,
EmployeeName,
DeptName,
JobName,
HOD,
Supervisor
)
AS
(
SELECT
a.[DATE],
a.EmployeeID,
a.Status,
1,a.EmployeeCode,a.EmployeeName,a.DeptName,a.JobName,a.HOD,a.Supervisor
FROM
tblEmployeeAttendance1 a

WHERE
    a.\[Date\] between @StartDate and @EndDate and (a.status='AB' OR a.Status='O') AND datepart(dw,a.\[Date\]) NOT IN (1,7)
-- RECURSIVE
UNION ALL

SELECT
    a.\[DATE\],
    a.EmployeeID,
    a.Status,
    CASE WHEN (a.Status = Parent.Status) THEN Parent.Days + 1 ELSE 1 END,
    a.EmployeeCode,a.EmployeeName,a.DeptName,a.JobName,a.HOD,a.Supervisor
FROM
    tblEmployeeAttendance1 a
INNER JOIN
    Dates parent
ON
    datediff(day, a.\[DATE\], DateAdd(day, 1, parent.EntryDate)) = 0
AND
    a.EmployeeID = parent.EmployeeNumber  where a.\[Date\] between @StartDate and @EndDate and (a.status='AB' OR a.Status='O') AND datepart(dw,a.\[Date\]) NOT IN (1,7)

)
SELECT * FROM Dates where days>=2 order by EmployeeNumber, EntryDate

I have added AND datepart(dw,a.[Date]) NOT IN (1,7), which will excludes weekly off days. this would work perfactly.