Although I have a solution that works for your sampel data, I suspect that you might have issues with "real" data:DECLARE @Foo TABLE
(
Record INT,
EmployeeId INT,
DOS INT,
SupervisorId INT,
ProblemCode CHAR(3)
)
INSERT @Foo VALUES
(1, 1, 20120101, 10, 'f25'),
(2, 1, 20120101, 10, 'f10'),
(3, 1, 20120101, 10, 'f01'),
(4, 1, 20120101, 26, 'f01'),
(5, 1, 20120101, 26, 'f55')
SELECT
*,
ROW_NUMBER() OVER
(
PARTITION BY
EmployeeID,
DOS,
CASE
WHEN
(
SupervisorID <> PreviousSupervisorID
AND ProblemCode = PreviousProblemCode
)
THEN
PreviousSupervisorID
ELSE
SupervisorID
END
ORDER BY
Record
) AS RowNum
FROM
(
SELECT
*,
LAG(SupervisorID, 1, NULL) OVER (ORDER BY Record) AS PreviousSupervisorID,
LAG(ProblemCode, 1, NULL) OVER (ORDER BY Record) AS PreviousProblemCode
FROM @foo
) AS A