No need to complicate things, the only thing needed here is correct window specification for the row_number function.

😎

;WITH HISTORY_BASE AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY HS.CUSTOMER

,HS.PLANNBR

,HS.SUBPLAN

,HS.STARTDATE

,HS.ENDDATE

ORDER BY HS.HISTORYMONTH

) AS CUST_RID

,HS.CUSTOMER

,HS.PLANNBR

,HS.SUBPLAN

,HS.STARTDATE

,HS.ENDDATE

FROM dbo.HISTORY HS

)

SELECT

HB.CUSTOMER

,HB.PLANNBR

,HB.SUBPLAN

,HB.STARTDATE

,HB.ENDDATE

FROM HISTORY_BASE HB

WHERE HB.CUST_RID = 1;

Results

CUSTOMER PLANNBR SUBPLAN STARTDATE ENDDATE

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

9111111 H1111LAC 006 2014-01-01 2014-05-31

9111111 H1111OC 010 2014-06-01 2999-12-31

9111112 H1111LAC 006 2014-01-01 2014-05-31

9111112 H1111LAC 018 2014-06-01 2999-12-31

9111113 H1111LAC 006 2014-01-01 2999-12-31

9111114 H1111LAC 006 2014-01-01 2014-02-28

9111114 H1111LAC 006 2014-04-01 2999-12-31

This can be improved by adding an index which results in an execution plan without the sort operator, something like this

CREATE UNIQUE NONCLUSTERED INDEX [IDX_DBO_HISTORY_POC_DD] ON [dbo].[HISTORY]

(

[CUSTOMER] ASC,

[PLANNBR] ASC,

[SUBPLAN] ASC,

[STARTDATE] ASC,

[ENDDATE] ASC,

[HISTORYMONTH] ASC

)

This brings the cost of execution for this code down to aprox. 5% of the previous code.