Consecutive Rows to fetch from SQL server table
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: