Consecutive Rows to fetch from SQL server table We have following table structure

+---------+------------+-------+---------+------------+---------+-----------+----------+--------+
| EmpCode |   InDate   | Shift | ArrTime |  OutDate   | DepTime | HoursWrkd | OverTime | Status |
+---------+------------+-------+---------+------------+---------+-----------+----------+--------+
|     110 | 04/11/2008 | GN    | 9:45    | 04/11/2008 | 0:00    | 0:00      | 0:00     | P P    |
|     115 | 03/11/2008 | GN    | 9:44    | 03/11/2008 | 18:00   | 8:16      | 0:00     | P P    |
|     105 | 03/11/2008 | GN    | 0:00    | 03/11/2008 | 0:00    | 0:00      | 0:00     | A A    |
|     105 | 04/11/2008 | GN    | 0:00    | 03/11/2008 | 0:00    | 0:00      | 0:00     | A A    |
|     110 | 03/11/2008 | GN    | 9:45    | 03/11/2008 | 0:00    | 0:00      | 0:00     | P P    |
|      86 | 29/11/2008 | HD    | 0:00    | 29/11/2008 | 0:00    | 0:00      | 0:00     | HD     |
|     105 | 05/11/2008 | GN    | 9:45    | 04/11/2008 | 0:00    | 0:00      | 0:00     | P P    |
+---------+------------+-------+---------+------------+---------+-----------+----------+--------+


We want those records where Employee is continuous absent for two days. Days interval will be dynamic.
So, this above result set should return

+---------+------------+-------+---------+------------+---------+-----------+----------+--------+
| EmpCode |   InDate   | Shift | ArrTime |  OutDate   | DepTime | HoursWrkd | OverTime | Status |
+---------+------------+-------+---------+------------+---------+-----------+----------+--------+
|     105 | 03/11/2008 | GN    | 0:00    | 03/11/2008 | 0:00    | 0:00      | 0:00     | A A    |
|     105 | 04/11/2008 | GN    | 0:00    | 03/11/2008 | 0:00    | 0:00      | 0:00     | A A    |
+---------+------------+-------+---------+------------+---------+-----------+----------+--------+


Any help will be highly appreciable.
Without knowing what you mean by dynamic days interval, the following is an example of how I might attempt this.

USE tempdb;
GO
CREATE TABLE dbo.ShiftStatii
(
    ShiftStatusID INT NOT NULL 
        CONSTRAINT PK_ShiftStatii 
        PRIMARY KEY CLUSTERED 
        IDENTITY(1,1)
    , StatusName VARCHAR(255) NOT NULL
);

INSERT INTO dbo.ShiftStatii (StatusName) 
VALUES ('Present'), ('Absent'), ('Holiday'); 

CREATE TABLE dbo.Employees
(
    EmpID INT NOT NULL 
        CONSTRAINT PK_Employees 
        PRIMARY KEY CLUSTERED 
        IDENTITY(1,1)
    , EmployeeName VARCHAR(255)
);

INSERT INTO dbo.Employees (EmployeeName) 
VALUES ('John'), ('Jane'), ('Joe');

CREATE TABLE dbo.Shifts
(
    ShiftID INT NOT NULL 
        CONSTRAINT PK_Shifts 
        PRIMARY KEY CLUSTERED 
        IDENTITY(1,1)
    , EmpID INT NOT NULL 
        CONSTRAINT FK_Shifts_EmpID
        FOREIGN KEY REFERENCES dbo.Employees(EmpID) 
        ON UPDATE NO ACTION ON DELETE NO ACTION
    , InDate DATE
    , ShiftStatusID INT NOT NULL 
        CONSTRAINT FK_Shifts_ShiftStatusID
        FOREIGN KEY REFERENCES dbo.ShiftStatii(ShiftStatusID) 
        ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO dbo.Shifts (EmpID, InDate, ShiftStatusID) 
VALUES (1, '20130201', 1)
    , (2, '20130201', 2)
    , (3, '20130201', 1)
    , (1, '20130202', 1)
    , (2, '20130202', 2)
    , (3, '20130202', 1)
    , (1, '20130203', 1)
    , (2, '20130203', 1)
    , (3, '20130203', 2)
    , (1, '20130205', 1)
    , (2, '20130205', 3)
    , (3, '20130205', 2);

SELECT E.EmployeeName, S.InDate, SS.StatusName
FROM dbo.Shifts S
    INNER JOIN dbo.Employees E ON S.EmpID = E.EmpID
    INNER JOIN dbo.ShiftStatii SS ON S.ShiftStatusID = SS.ShiftStatusID
ORDER BY E.EmployeeName, S.InDate


The data for my test, therefore, is:



SELECT E.EmployeeName, S1.InDate, S2.InDate
FROM (
        SELECT ROW_NUMBER() 
            OVER (PARTITION BY S.EmpID ORDER BY S.InDate) AS EmpRowNum, *
        FROM dbo.Shifts S) S1
    INNER JOIN (
        SELECT ROW_NUMBER() 
            OVER (PARTITION BY S.EmpID ORDER BY S.InDate) AS EmpRowNum, *
        FROM dbo.Shifts S
        ) S2 ON S1.EmpID = S2.EmpID AND S1.EmpRowNum = S2.EmpRowNum - 1
INNER JOIN dbo.Employees E ON S1.EmpID = E.EmpID
WHERE S1.ShiftStatusID = 2 AND S2.ShiftStatusID = 2


The results of this are: