September 20, 2012 at 7:30 am
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
September 20, 2012 at 8:23 am
I use a calendar table for that kind of thing.
Create a table of all dates for the year (or decade, whatever length seems appropriate). Mark the ones that are workdays.
create table dbo.Calendar (
[Date] Date primary key,
WorkDay bit not null default(1));
Something like that. You can add more columns as you need more data (like Fiscal Year, Quarter, etc.).
Then join from attendance to calendar and back to attendance. Becomes very easy to determine if two (or more) days in a row were missed, even over weekends and holidays.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply