First of all, good job with the data and the DDL, made this easy;-)

Here is a quick window function solution, self explanatory and should get you passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

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

CREATE TABLE [dbo].[ABC](

[Hno] [nchar](10) NULL,

[Status] [smallint] NULL,

[Date] [datetime] NULL

) ON [PRIMARY];

Insert into [dbo].[ABC] values (100,0,getdate()-6)

Insert into [dbo].[ABC] values (100,0,getdate()-5)

Insert into [dbo].[ABC] values (100,1,getdate()-4)

Insert into [dbo].[ABC] values (100,1,getdate()-3)

Insert into [dbo].[ABC] values (100,0,getdate()-2)

Insert into [dbo].[ABC] values (100,0,getdate()-1)

Insert into [dbo].[ABC] values (100,1,getdate())

;

;WITH STATUS_MARKED AS

(

SELECT

A.Hno

,A.[Status]

,CASE

WHEN LAG(A.[Status],1,A.[Status]) OVER

(

PARTITION BY (SELECT NULL)

ORDER BY A.[Date]

) = A.[Status] THEN 0

ELSE 1

END AS ST_FLG

,A.[Date]

FROM dbo.ABC A

)

,STATUS_SET AS

(

SELECT

SM.Hno

,SM.[Status]

,SM.[Date]

,SUM(SM.ST_FLG) OVER

(

PARTITION BY (SELECT NULL)

ORDER BY SM.[Date]

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS ST_FLG

FROM STATUS_MARKED SM

)

,FINAL_SET AS

(

SELECT

ST.Hno

,ST.[Status]

,ST.[Date]

,ROW_NUMBER() OVER

(

PARTITION BY ST.ST_FLG

ORDER BY ST.[Date]

) AS ST_RID

FROM STATUS_SET ST

)

SELECT

FS.Hno

,FS.[Status]

,FS.[Date]

FROM FINAL_SET FS

WHERE FS.ST_RID = 1

;

Results

Hno Status Date

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

100 0 2014-12-19 21:55:44.333

100 1 2014-12-21 21:55:44.333

100 0 2014-12-23 21:55:44.333

100 1 2014-12-25 21:55:44.337