Quick solution, similar to the one Ozzmodiar posted, with an additional POC index which eliminates any sorting operations in the execution plan.

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.testTable') IS NOT NULL DROP TABLE dbo.testTable;

create table dbo.testTable

(

id TINYINT NOT NULL

,cust_id INT NOT NULL

,sDate DATETIME NOT NULL

,budget DECIMAL(8,2) NOT NULL

);

INSERT INTO dbo.testTable (id,cust_id,sDate,budget)

VALUES

(1,1,'2015-01-01',100)

,(2,1,'2015-02-01',100)

,(9,1,'2015-03-01',500)

,(4,1,'2015-04-01',100)

,(3,2,'2015-01-01',50)

,(7,2,'2015-02-01',10)

,(5,2,'2015-03-01',50)

,(8,2,'2015-04-01',50)

;

/* POC (Partition Order Covering) index for dbo.testTable */

CREATE UNIQUE NONCLUSTERED INDEX UNQNCLIDX_DBO_TEST_TABLE_CUST_ID_SDATE_INCL_BUDGET ON dbo.testTable

(

cust_id ASC

,sDate ASC

)

INCLUDE

(

budget

-- ,id /* Add this column if needed in the output */

);

;WITH BASE_DATA AS

(

SELECT

TT.cust_id

,TT.sDate

,TT.budget

,CASE

WHEN TT.budget = LAG(TT.budget,1,-1) OVER

(

PARTITION BY TT.cust_id

ORDER BY TT.sDate

) THEN 0

ELSE 1

END AS CHANGE_FLAG

FROM dbo.testTable TT

)

SELECT

BD.cust_id

,BD.sDate

,BD.budget

FROM BASE_DATA BD

WHERE BD.CHANGE_FLAG = 1;

Results

cust_id sDate budget

----------- ----------------------- --------

1 2015-01-01 00:00:00.000 100.00

1 2015-03-01 00:00:00.000 500.00

1 2015-04-01 00:00:00.000 100.00

2 2015-01-01 00:00:00.000 50.00

2 2015-02-01 00:00:00.000 10.00

2 2015-03-01 00:00:00.000 50.00