none
Tuffest query to find employee who absnet for continous 3 working days

    Question

  • i am looking for one query where i will get only those employee who is absnet for continue 3 working days

    like absnet for monday,tuesday,wednesday

    MY QYERY is


    select DISTINCT
    TAB.RTT_EMP_STAFFID,
    TAB.EMP_FIRSTNAME  "Employee name",
    TAB.EMP_DATEOFJOINING "Date of Joining",
    TAB.RTT_ATTENDANCE_DATE "Attendence Date",
    B.RTT_STATUS Status
    from ATT_RPT_CALENDAR B
    LEFT JOIN ATTENDANCE_REGISTER A  ON A.EMP_STAFFID=B.RTT_EMP_STAFFID AND A.ATTENDANCE_DATE=B.RTT_ATTENDANCE_DATE
    INNER JOIN (
    SELECT RTT_EMP_STAFFID,RTT_ATTENDANCE_DATE, MAX(RTT_CRT_ON) DATE1,EMP.EMP_FIRSTNAME,
    EMP_MIDDLENAME,EMP_LASTNAME,EMP_DATEOFJOINING,EMP_MAILID,EMP_REPORTINGTO
    FROM ATT_RPT_CALENDAR
    INNER JOIN ERM_EMPLOYEE_MASTER EMP ON EMP.EMP_STAFFID= ATT_RPT_CALENDAR.RTT_EMP_STAFFID
    GROUP BY
    RTT_ATTENDANCE_DATE,RTT_EMP_STAFFID,
    EMP.EMP_FIRSTNAME,EMP_MIDDLENAME,EMP_LASTNAME,EMP_DATEOFJOINING,EMP_MAILID,EMP_REPORTINGTO
    ) TAB ON TAB.RTT_ATTENDANCE_DATE=B.RTT_ATTENDANCE_DATE AND TAB.DATE1 = B.RTT_CRT_ON AND TAB.RTT_EMP_STAFFID=B.RTT_EMP_STAFFID
    WHERE B.RTT_ATTENDANCE_DATE>='2012-04-16'  and B.RTT_ATTENDANCE_DATE<='2012-05-15' 
    ORDER BY TAB.RTT_ATTENDANCE_DATE DESC

     output is

    RTT_EMP_STAFFID Employee name Date of Joining Attendence Date Status
    C1038                Udayakumar 5/6/10 5/15/12 W  
    C1038                Udayakumar 5/6/10 5/14/12 P  
    C1038                Udayakumar 5/6/10 5/13/12 P  
    C1038                Udayakumar 5/6/10 5/12/12 P  
    C1038                Udayakumar 5/6/10 5/11/12 P  
    C1038                Udayakumar 5/6/10 5/10/12 P  
    C1038                Udayakumar 5/6/10 5/8/12 A  
    C1038                Udayakumar 5/6/10 5/7/12 P  
    C1038                Udayakumar 5/6/10 5/6/12 W* 
    C1038                Udayakumar 5/6/10 5/5/12
    C1038                Udayakumar 5/6/10 5/4/12
    C1038                Udayakumar 5/6/10 5/3/12 A
    C1038                Udayakumar 5/6/10 5/2/12 A  
    C1038                Udayakumar 5/6/10 5/1/12 H  
    C1038                Udayakumar 5/6/10 4/30/12 P  
    C1038                Udayakumar 5/6/10 4/29/12 W* 
    C1038                Udayakumar 5/6/10 4/28/12 P  
    C1038                Udayakumar 5/6/10 4/27/12 A  
    C1038                Udayakumar 5/6/10 4/26/12 P  
    C1038                Udayakumar 5/6/10 4/25/12 P  
    C1038                Udayakumar 5/6/10 4/24/12 AR 
    C1038                Udayakumar 5/6/10 4/23/12 P  
    C1038                Udayakumar 5/6/10 4/22/12 W* 
    C1038                Udayakumar 5/6/10 4/21/12 A  
    C1038                Udayakumar 5/6/10 4/20/12 P  
    C1038                Udayakumar 5/6/10 4/19/12 P  
    C1038                Udayakumar 5/6/10 4/18/12 P  
    C1038                Udayakumar 5/6/10 4/17/12 P  
    C1038                Udayakumar 5/6/10 4/16/12 P                   

    where status=A means absent

    W=weekly off

    i shuold get only thiose employee who is absnet contiuos for 3 working days

    regards,

    Vipin jha




    Thankx & regards, Vipin jha MCP

    Wednesday, June 13, 2012 9:59 AM

Answers

  • This is a classic problem called 'Island problem'. Google 'islands and gaps' for more details.

    Here is one method to acheeve the result:

    DECLARE @Attendance TABLE
    (
    RTT_EMP_STAFFID	VARCHAR(10),
    EmployeeName VARCHAR(20),
    DateofJoining DATETIME,
    AttendenceDate DATETIME,
    Status VARCHAR(2)
    )
    
    INSERT INTO @Attendance VALUES
    ('C1038', 'Udayakumar', '05/06/2010', '05/15/2012', 'W'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/14/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/13/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/12/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/11/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/10/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/08/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/07/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/06/2012', 'W*'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/05/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/04/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/03/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/02/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/01/2012', 'H'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/30/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/29/2012', 'W*'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/28/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/27/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/26/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/25/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/24/2012', 'AR'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/23/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/22/2012', 'W*'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/21/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/20/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/19/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/18/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/17/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/16/2012', 'P')
    
    
    ;WITH D AS
    (
    SELECT RTT_EMP_STAFFID, AttendenceDate, 
    	DATEADD(
    		day, -ROW_NUMBER() OVER(PARTITION BY RTT_EMP_STAFFID ORDER BY AttendenceDate), AttendenceDate
    		) AS grp
    FROM @Attendance WHERE Status = 'A'
    ),
    AttCTE
    AS
    (
    SELECT 
    	RTT_EMP_STAFFID, MIN(AttendenceDate) AS StartDate, MAX(AttendenceDate) AS EndDate
    	,ConsecutiveDays = DATEDIFF(day, MIN(AttendenceDate), MAX(AttendenceDate)) + 1
    FROM D
    GROUP BY RTT_EMP_STAFFID, grp
    )
    SELECT * FROM AttCTE WHERE ConsecutiveDays >= 3
    

    This is a slightly modified version of a script from Itzik Ben-Gaan's book 'Inside Microsoft SQL Server 2008 T-SQL Querying'

    Krishnakumar S

    • Proposed as answer by Naomi NModerator Wednesday, June 13, 2012 12:35 PM
    • Marked as answer by Vipin jha Thursday, June 14, 2012 5:04 AM
    Wednesday, June 13, 2012 11:42 AM

All replies

  • i am looking for one query where i will get only those employee who is absnet for continue 3 working days

    like absnet for monday,tuesday,wednesday

    MY QYERY is


    select DISTINCT
    TAB.RTT_EMP_STAFFID,
    TAB.EMP_FIRSTNAME  "Employee name",
    TAB.EMP_DATEOFJOINING "Date of Joining",
    TAB.RTT_ATTENDANCE_DATE "Attendence Date",
    B.RTT_STATUS Status
    from ATT_RPT_CALENDAR B
    LEFT JOIN ATTENDANCE_REGISTER A  ON A.EMP_STAFFID=B.RTT_EMP_STAFFID AND A.ATTENDANCE_DATE=B.RTT_ATTENDANCE_DATE
    INNER JOIN (
    SELECT RTT_EMP_STAFFID,RTT_ATTENDANCE_DATE, MAX(RTT_CRT_ON) DATE1,EMP.EMP_FIRSTNAME,
    EMP_MIDDLENAME,EMP_LASTNAME,EMP_DATEOFJOINING,EMP_MAILID,EMP_REPORTINGTO
    FROM ATT_RPT_CALENDAR
    INNER JOIN ERM_EMPLOYEE_MASTER EMP ON EMP.EMP_STAFFID= ATT_RPT_CALENDAR.RTT_EMP_STAFFID
    GROUP BY
    RTT_ATTENDANCE_DATE,RTT_EMP_STAFFID,
    EMP.EMP_FIRSTNAME,EMP_MIDDLENAME,EMP_LASTNAME,EMP_DATEOFJOINING,EMP_MAILID,EMP_REPORTINGTO
    ) TAB ON TAB.RTT_ATTENDANCE_DATE=B.RTT_ATTENDANCE_DATE AND TAB.DATE1 = B.RTT_CRT_ON AND TAB.RTT_EMP_STAFFID=B.RTT_EMP_STAFFID
    WHERE B.RTT_ATTENDANCE_DATE>='2012-04-16'  and B.RTT_ATTENDANCE_DATE<='2012-05-15' 
    ORDER BY TAB.RTT_ATTENDANCE_DATE DESC

     output is

    RTT_EMP_STAFFID Employee name Date of Joining Attendence Date Status
    C1038                Udayakumar 5/6/10 5/15/12 W  
    C1038                Udayakumar 5/6/10 5/14/12 P  
    C1038                Udayakumar 5/6/10 5/13/12 P  
    C1038                Udayakumar 5/6/10 5/12/12 P  
    C1038                Udayakumar 5/6/10 5/11/12 P  
    C1038                Udayakumar 5/6/10 5/10/12 P  
    C1038                Udayakumar 5/6/10 5/8/12 A  
    C1038                Udayakumar 5/6/10 5/7/12 P  
    C1038                Udayakumar 5/6/10 5/6/12 W* 
    C1038                Udayakumar 5/6/10 5/5/12
    C1038                Udayakumar 5/6/10 5/4/12
    C1038                Udayakumar 5/6/10 5/3/12 A
    C1038                Udayakumar 5/6/10 5/2/12 A  
    C1038                Udayakumar 5/6/10 5/1/12 H  
    C1038                Udayakumar 5/6/10 4/30/12 P  
    C1038                Udayakumar 5/6/10 4/29/12 W* 
    C1038                Udayakumar 5/6/10 4/28/12 P  
    C1038                Udayakumar 5/6/10 4/27/12 A  
    C1038                Udayakumar 5/6/10 4/26/12 P  
    C1038                Udayakumar 5/6/10 4/25/12 P  
    C1038                Udayakumar 5/6/10 4/24/12 AR 
    C1038                Udayakumar 5/6/10 4/23/12 P  
    C1038                Udayakumar 5/6/10 4/22/12 W* 
    C1038                Udayakumar 5/6/10 4/21/12 A  
    C1038                Udayakumar 5/6/10 4/20/12 P  
    C1038                Udayakumar 5/6/10 4/19/12 P  
    C1038                Udayakumar 5/6/10 4/18/12 P  
    C1038                Udayakumar 5/6/10 4/17/12 P  
    C1038                Udayakumar 5/6/10 4/16/12 P                   

    where status=A means absent

    W=weekly off

    i shuold get only thiose employee who is absnet contiuos for 3 working days

    regards,

    Vipin jha



    Thankx & regards, Vipin jha MCP

    Wednesday, June 13, 2012 6:22 AM
  • Sorry ,untested.

    SELECT * FROM 

    (

    SELECT *,COUNT(*) OVER (PARTITION BY Status) cnt FROM tbl

    ) AS Der WHERE cnt =3


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, June 13, 2012 7:15 AM
    Answerer
  • Hi,

    can you try this?

    with cte
    as(select empid,pdate,present,RANK()over(partition by empid,present order by empid,pdate,present) 
    RN from emp)
    select empid ,Max(Pdate) from cte where RN>=3 and present='A' group by empid

    Regards
    satheesh

    Wednesday, June 13, 2012 7:30 AM
  • I AM NOT GETTING DATA

    Thankx & regards, Vipin jha MCP

    Wednesday, June 13, 2012 7:46 AM
  • This is a very interesting t-sql problem. But certainly there is a solution.

    Before I start, I will have a couple of assumption:

    1. I assume that status 'H' is for holiday so if holiday is between leave I will consider as consective leave

    2. I assume that you might have 2 or more consecutive working off days or holidays. if there are between the leave the leave will also be considered as consecutive leave

    3. I don't know what is 'AR' in your status so I ignore it but you can put it back by your self once you think my solution works:

    --Test Data preparation
    
    Declare @Employee Table(EmpID char(5));
    Declare @Workdate Table(WorkingDate datetime);
    Declare @EmployAttendence Table (EmpID char(5), WorkingDate date, Status char(1) default 'p')
    
    Insert into @Employee
    Values
    ('C1038')
    , ('C1055');
    
    Insert into @Workdate
    Select dateadd(d, number, '2012-04-16')
    From	Master..spt_values
    Where	Type = 'P'
    		And Number between 1 and 31
    
    Insert into @EmployAttendence (EmpID, workingdate)
    Select EmpID, workingdate
    From	@Employee, @Workdate
    Order by EmpID, workingdate
    
    
    --set absent date for c1038
    Update  @EmployAttendence
    Set		Status = 'A'
    Where	EmpID = 'C1038'
    		And
    		(	WorkingDate in ( '2012-04-17', '2012-05-07')
    				or
    			WorkingDate Between '2012-04-20' and '2012-04-24'
    				or
    			WorkingDate Between '2012-04-28' and '2012-05-03'
    				or
    			WorkingDate Between '2012-05-10' and '2012-05-13'
    		)
    
    --set absent date for c10355
    Update  @EmployAttendence
    Set		Status = 'A'
    Where	EmpID = 'C1055'
    		And
    		(	WorkingDate in ( '2012-04-19', '2012-05-03')
    				or
    			WorkingDate Between '2012-04-23' and '2012-04-27'
    				or
    			WorkingDate Between '2012-05-13' and '2012-05-14'
    		)
    		
    --set weekly off date
    Update  @EmployAttendence
    Set		Status = 'W'
    Where	WorkingDate in ('2012-04-22', '2012-04-23','2012-04-29', '2012-05-06', '2012-05-15')
    
    --set public holiday
    Update  @EmployAttendence
    Set		Status = 'H'
    Where	WorkingDate in ( '2012-04-30', '2012-05-01')		
    
    
    --Select * From @EmployAttendence
    ----Solution Starts here
    
    ;With DateGrouping
    As
    (
    Select EmpID, WorkingDate, Status--, Status_Group
    		, DATEADD(Day, -1 * ROW_NUMBER() Over(Partition By EmpID Order by WorkingDate), WorkingDate) As Date_Group
    From	@EmployAttendence
    Where	Status in ('A', 'W', 'H')
    --Order by 1, 2	
    )
    
    Select	EmpID, Min(WorkingDate) As Absent_Start, MAX(WorkingDate) As Absent_End, COUNT(*) As Absent_Count
    From	DateGrouping
    Where	Status = 'A'
    Group By EmpId, Date_Group			
    Having COUNT(*) > 2
    Order by EmpID, Absent_Start

    --Results:

    EmpID Absent_Start Absent_End Absent_Count
    C1038 2012-04-20 2012-04-24 3
    C1038 2012-04-28 2012-05-03 3
    C1038 2012-05-10 2012-05-13 4
    C1055 2012-04-24 2012-04-27 4


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!





    Wednesday, June 13, 2012 10:53 AM
  • Hi,

    whats your desired output? the one posted looks like sample data

    Regards
    satheesh

    Wednesday, June 13, 2012 10:56 AM
  • I just tried this and working

    changes may be needed if depending on your desired output.

    CREATE TABLE [dbo].[emp](
    	[EMPID] [varchar](5) NULL,
    	[Empname] [varchar](50) NULL,
    	[DoJ] [date] NULL,
    	[AttendenceDay]  [date] NULL,
    	[Status] [char](2) NULL
    );
    
    insert into emp
    values
    ('C1038','Udayakumar','5/6/2010','5/15/2012','W'),
    ('C1038','Udayakumar','5/6/2010','5/14/2012','P'),
    ('C1038','Udayakumar','5/6/2010','5/13/2012','P'),
    ('C1038','Udayakumar','5/6/2010','5/12/2012','P'),
    ('C1038','Udayakumar','5/6/2010','5/11/2012','P'),
    ('C1038','Udayakumar','5/6/2010','5/10/2012','P'),
    ('C1038','Udayakumar','5/6/2010','5/8/2012','A'),
    ('C1038','Udayakumar','5/6/2010','5/7/2012','P'),
    ('C1038','Udayakumar','5/6/2010','5/6/2012','W*'),
    ('C1038','Udayakumar','5/6/2010','5/5/2012','A'),
    ('C1038','Udayakumar','5/6/2010','5/4/2012','A'),
    ('C1038','Udayakumar','5/6/2010','5/3/2012','A'),
    ('C1038','Udayakumar','5/6/2010','5/2/2012','A'),
    ('C1038','Udayakumar','5/6/2010','5/1/2012','H'),
    ('C1038','Udayakumar','5/6/2010','4/30/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/29/2012','W*'),
    ('C1038','Udayakumar','5/6/2010','4/28/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/27/2012','A'),
    ('C1038','Udayakumar','5/6/2010','4/26/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/25/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/24/2012','AR'),
    ('C1038','Udayakumar','5/6/2010','4/23/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/22/2012','W*'),
    ('C1038','Udayakumar','5/6/2010','4/21/2012','A'),
    ('C1038','Udayakumar','5/6/2010','4/20/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/19/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/18/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/17/2012','P'),
    ('C1038','Udayakumar','5/6/2010','4/16/2012','P');
    with cte
    as(select empid,AttendenceDay,status,RANK()over(partition by empid,status order by empid,AttendenceDay,status) 
    RN from emp)
    select empid ,Max(AttendenceDay) from cte where RN>=3 and status='A' group by empid
    ;

    Regards
    satheesh

    Wednesday, June 13, 2012 11:06 AM
  • This is a classic problem called 'Island problem'. Google 'islands and gaps' for more details.

    Here is one method to acheeve the result:

    DECLARE @Attendance TABLE
    (
    RTT_EMP_STAFFID	VARCHAR(10),
    EmployeeName VARCHAR(20),
    DateofJoining DATETIME,
    AttendenceDate DATETIME,
    Status VARCHAR(2)
    )
    
    INSERT INTO @Attendance VALUES
    ('C1038', 'Udayakumar', '05/06/2010', '05/15/2012', 'W'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/14/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/13/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/12/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/11/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/10/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/08/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/07/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/06/2012', 'W*'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/05/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/04/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/03/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/02/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '05/01/2012', 'H'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/30/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/29/2012', 'W*'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/28/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/27/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/26/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/25/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/24/2012', 'AR'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/23/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/22/2012', 'W*'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/21/2012', 'A'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/20/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/19/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/18/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/17/2012', 'P'),
    ('C1038', 'Udayakumar', '05/06/2010', '04/16/2012', 'P')
    
    
    ;WITH D AS
    (
    SELECT RTT_EMP_STAFFID, AttendenceDate, 
    	DATEADD(
    		day, -ROW_NUMBER() OVER(PARTITION BY RTT_EMP_STAFFID ORDER BY AttendenceDate), AttendenceDate
    		) AS grp
    FROM @Attendance WHERE Status = 'A'
    ),
    AttCTE
    AS
    (
    SELECT 
    	RTT_EMP_STAFFID, MIN(AttendenceDate) AS StartDate, MAX(AttendenceDate) AS EndDate
    	,ConsecutiveDays = DATEDIFF(day, MIN(AttendenceDate), MAX(AttendenceDate)) + 1
    FROM D
    GROUP BY RTT_EMP_STAFFID, grp
    )
    SELECT * FROM AttCTE WHERE ConsecutiveDays >= 3
    

    This is a slightly modified version of a script from Itzik Ben-Gaan's book 'Inside Microsoft SQL Server 2008 T-SQL Querying'

    Krishnakumar S

    • Proposed as answer by Naomi NModerator Wednesday, June 13, 2012 12:35 PM
    • Marked as answer by Vipin jha Thursday, June 14, 2012 5:04 AM
    Wednesday, June 13, 2012 11:42 AM
  • Hi Krishnakumar, your idea and suggestion is correct which is indeed same as my concept but your solution won't work even it is working for your example data.

    How about the working off dates and holidays in between the Absence??


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Wednesday, June 13, 2012 9:54 PM
  • Post DDL next time. Here is a guess with ISO-11179 names



    SELECT DISTINCT staff_id
    FROM Attendance_Register AS A2
    WHERE 'A'
    = ALL (SELECT attendance_status
    FROM Attendance_Register AS A2
    WHERE A2.attendance_date
    BETWEEN A1.attendance_date
    AND DATEADD(DD, A1.attendance_date, 2))



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, June 14, 2012 3:59 AM
  • Thank you sir very much,

    my problem is solved just becouse of you

    thank you again

    regards,

    Vipin jha


    Thankx & regards, Vipin jha MCP

    Thursday, June 14, 2012 5:04 AM