Database

Matt U. wrote:

But is this the best way to go about it?

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

WITH cteStartingPoints As
(
    -- Find the rows with no row ending on the previous day:
    SELECT
        A.EmpCode,
        A.StartDate,
        ROW_NUMBER() OVER (ORDER BY A.StartDate) As RN
    FROM
        YourTable As A
    WHERE
        Not Exists
        (
            SELECT 1
            FROM YourTable As B
            WHERE B.EmpCode = A.EmpCode
            And B.EndDate = DateAdd(day, -1, A.StartDate)
        )
),
cteEndingPoints As
(
    -- Find the rows with no row starting on the next day:
    SELECT
        A.EmpCode,
        A.EndDate,
        ROW_NUMBER() OVER (ORDER BY A.StartDate) As RN
    FROM
        YourTable As A
    WHERE
        Not Exists
        (
            SELECT 1
            FROM YourTable As B
            WHERE B.EmpCode = A.EmpCode
            And B.StartDate = DateAdd(day, 1, A.EndDate)
        )
)
SELECT
    S.EmpCode,
    S.StartDate,
    E.EndDate
FROM
    cteStartingPoints As S
    INNER JOIN cteEndingPoints As E
    ON E.EmpCode = S.EmpCode
    And E.RN = S.RN
;

Richard Deeming wrote:

but the obvious solution would be