How to find persons who are absent continuously for n number of days?

Question

Database: MS SQL 2005

Table:

EmployeeNumber | EntryDate | Status

Sample Data:

200 | 3/1/2009 | P
200 | 3/2/2009 | A
200 | 3/3/2009 | A
201 | 3/1/2009 | A
201 | 3/2/2009 | P

Where P is present, A is absent. I have tried row_number over partion. But it does not generate the sequence which I expect.

For the above data the sequence I expect is 1 1 2 1 1

SELECT EmployeeNumber, EntryDate,Status
    ROW_NUMBER() OVER (
    PARTITION BY EmployeeNumber, Status
    ORDER BY EmployeeNumber,EntryDate    ) AS 'RowNumber'
    FROM [Attendance]

Answer 1

Does this help you?
It doesn't produce the sequence you ask (No idea how to do that) but it does give you the ammount of consecutive days someone has been absent.

DECLARE @Attendance TABLE (EmployeeNumber INTEGER, EntryDate DATETIME, Status VARCHAR(1))

INSERT INTO @Attendance VALUES (200, '03/01/2009', 'P')
INSERT INTO @Attendance VALUES (200, '03/02/2009', 'A')
INSERT INTO @Attendance VALUES (200, '03/03/2009', 'A')
INSERT INTO @Attendance VALUES (200, '03/04/2009', 'A')
INSERT INTO @Attendance VALUES (200, '04/04/2009', 'A')
INSERT INTO @Attendance VALUES (200, '04/05/2009', 'A')
INSERT INTO @Attendance VALUES (201, '03/01/2009', 'A')
INSERT INTO @Attendance VALUES (201, '03/02/2009', 'A')
INSERT INTO @Attendance VALUES (201, '03/03/2009', 'P')


SELECT a1.EmployeeNumber, [Absent] = COUNT(*) + 1
FROM @Attendance a1
     INNER JOIN @Attendance a2 ON a1.EntryDate = a2.EntryDate - 1 
                                  AND a1.EmployeeNumber = a2.EmployeeNumber
                                  AND a1.Status = a2.Status
GROUP BY a1.EmployeeNumber

Answer 2

You could use recursion, similar to what I have done here. It seems though that your problem is a little simpler, and since SQL Server limits recursion to 99, this might not work for people who are absent a lot. Let me think about this a few minutes.

If you have a row for every single day, go with Lieven's join.

Answer 3

i'm not sure I follow what you're wanting with the 1 1 2 1 1 sequence, but simply adding an order by to your original query produces that sequence...

SELECT  EmployeeNumber, 
     EntryDate,
     Status,
     ROW_NUMBER() OVER (PARTITION BY EmployeeNumber, Status ORDER BY EmployeeNumber, EntryDate) AS 'RowNumber'    
FROM    Attendance
ORDER BY EmployeeNumber, EntryDate


/*
EmployeeNumber EntryDate               Status RowNumber
-------------- ----------------------- ------ --------------------
200            2009-03-01 00:00:00     P      1
200            2009-03-02 00:00:00     A      1
200            2009-03-03 00:00:00     A      2
201            2009-03-01 00:00:00     A      1
201            2009-03-02 00:00:00     P      1

(5 row(s) affected)
*/

Answer 4

You should be able to do this with a CTE in SQL 2005. Stealing Lievens data:

DECLARE @Attendance TABLE (EmployeeNumber INTEGER, EntryDate DATETIME, Status VARCHAR(1))

INSERT INTO @Attendance VALUES (200, '03/01/2009', 'P')
INSERT INTO @Attendance VALUES (200, '03/02/2009', 'A')
INSERT INTO @Attendance VALUES (200, '03/03/2009', 'A')
INSERT INTO @Attendance VALUES (200, '03/04/2009', 'A')
INSERT INTO @Attendance VALUES (200, '04/04/2009', 'A')
INSERT INTO @Attendance VALUES (200, '04/05/2009', 'A')
INSERT INTO @Attendance VALUES (201, '03/01/2009', 'A')
INSERT INTO @Attendance VALUES (201, '03/02/2009', 'A')
INSERT INTO @Attendance VALUES (201, '03/03/2009', 'P');

Then use this CTE to extract the sequence:

 WITH Dates 
    (
        EntryDate,
        EmployeeNumber,
        Status,
        Days
    ) 
    AS
    (
        SELECT
            a.EntryDate,
            a.EmployeeNumber,
            a.Status,
            1
        FROM
            @Attendance a

        WHERE
            a.EntryDate = (SELECT MIN(EntryDate) FROM @Attendance)


        -- RECURSIVE    
        UNION ALL

        SELECT
            a.EntryDate, 
            a.EmployeeNumber,
            a.Status,
            CASE WHEN (a.Status = Parent.Status) THEN Parent.Days + 1 ELSE 1 END
        FROM
            @Attendance a
        INNER JOIN
            Dates parent
        ON
            datediff(day, a.EntryDate, DateAdd(day, 1, parent.EntryDate)) = 0
        AND
            a.EmployeeNumber = parent.EmployeeNumber
    )

    SELECT * FROM Dates order by EmployeeNumber, EntryDate

Although as a final note the sequence does seem strange to me, depending on your requirements there may be a better way of aggregating the data? Never the less, this will produce the sequence you require