Gap Island Problems Solutions




CREATE TABLE [dbo].[test](
	[id] [int] NULL,
	[state] [varchar](50) NULL,
	[from_date] [datetime] NULL
) ON [PRIMARY]

GO
 
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (1, N'Initiated', CAST(0x0000A2D8002C7EA0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (2, N'Initiated', CAST(0x0000A2D8004D7420 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (3, N'Processing', CAST(0x0000A2D8006E69A0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (4, N'Processing', CAST(0x0000A2D8007E57C0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (5, N'Processing', CAST(0x0000A2D800827670 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (6, N'completed', CAST(0x0000A2D8008F5F20 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (7, N'completed', CAST(0x0000A2D8009191A0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (8, N'Initiated', CAST(0x0000A2D8009FD9E0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (9, N'Initiated', CAST(0x0000A2D800B054A0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (10, N'Processing', CAST(0x0000A2D800C0CF60 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (11, N'Processing', CAST(0x0000A2D800C4A7C0 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (12, N'completed', CAST(0x0000A2D800C88020 AS DateTime))
GO
INSERT [dbo].[test] ([id], [state], [from_date]) VALUES (13, N'completed', CAST(0x0000A2D800CDFE60 AS DateTime))
GO


--Option 1

;with mycte as(
select  id, state, from_date
, row_number() Over(  Order by id) - row_number() Over(Partition By state Order by id) rndelta  
from  test)

,mycte1 as (Select  id, state, from_date
, row_number() Over(Partition By state,rndelta Order by from_date DESC) rn 
from mycte
)
Select id, state, from_date from mycte1
WHERE rn=1
order by id


--Option 2

SELECT id,state,from_date, seq, Next
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY COALESCE(Next,-1) ORDER BY from_date DESC) AS Seq
FROM test t
OUTER APPLY (SELECT MIN(id) AS next
             FROM test
             WHERE id > t.id
             AND state <> t.state
             )t1
)t
WHERE Seq = 1
order by id



--Option 3

;with cte
as
(
	select *,1 as flag from test
	where id=1
	UNION ALL
	select t.*,CASE WHEN t.state=c.state then c.flag else c.flag+1 end as flag
	from test t
	inner join cte c on t.id=c.id+1
)
select id,state,from_Date
from
(
	select id,state,from_Date,row_number() over(partition by flag order by from_Date desc) rnum
	from cte
) tt
where rnum=1



--http://social.msdn.microsoft.com/Forums/en-US/a3426959-5c00-4449-8f5c-ef7963a326ca/how-to-retrieve-the-max-date-part-value-of-the-continuous-duplicate-record-in-table?forum=transactsql


 

It seems the recursive solution is the winner from checking the execution plan.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s