Coninuous absent query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Coninuous absent query

Author

Topic

rahulpatel112
Starting Member

4 Posts

Posted - 2012-09-20 : 09:26:45

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

R.J.PATEL

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 09:42:56

The following is not complete code, but just a thought on how you might do this:

First, create a cte with a row_number.
Then, in your current query, use this cte instead of tblEmployeeAttendance1 and join on RN instead of the difference in dates.

WITH cte1 AS
(
	SELECT *, 
	ROW_NUMBER() OVER (PARTITION BY EmployeeNumber ORDER BY Date) AS RN
	FROM tblEmployeeAttendance1
	WHERE status <> 'Vacation'
)
-- rest of your query here, but join on RN in the recursive part
-- and use cte1 instead of tblEmployeeAttendance1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources