You could start with something like this. It gives you back almost what you're looking for, it just leaves out the cases where the employee pay rate never changes :

CREATE TABLE #PayScale

(

ID INT IDENTITY PRIMARY KEY,

EmpName VARCHAR(30),

PayDate DATETIME,

WageType VARCHAR(30),

PayRate DECIMAL(18, 2)

)

INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)

VALUES ('ABC', '2014-10-22', 'Regular', '10.20')

INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)

VALUES ('XYZ', '2014-10-22', 'Regular', '9.30')

INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)

VALUES ('ABC', '2014-10-23', 'Regular', '10.20')

INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)

VALUES ('ABC', '2014-10-24', 'Regular', '11.50')

INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)

VALUES ('ABC', '2014-10-25', 'Regular', '11.20')

INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)

VALUES ('XYZ', '2014-10-25', 'Regular', '9.30')

INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)

VALUES ('ABC', '2014-10-26', 'Regular', '10.20')

INSERT INTO #PayScale (EmpName, PayDate, WageType, PayRate)

VALUES ('XYZ', '2014-10-26', 'Regular', '9.30')

;

WITH cte AS

(

SELECT p1.*, p2.PayDate AS NextPayDate, p2.WageType AS NextWageType, p2.PayRate AS NextPayRate, ROW_NUMBER() OVER (PARTITION BY p1.EmpName, p1.PayDate ORDER BY p2.PayDate ASC) AS rowNum

FROM #PayScale p1

LEFT JOIN #PayScale p2 ON p2.PayDate > p1.PayDate AND p2.EmpName = p1.EmpName

)

SELECT EmpName, PayDate, PayRate

FROM cte

WHERErowNum = 1

AND NextPayRate <> PayRate

DROP TABLE #PayScale