Islands (Take # 2)

Question

I have what I learned on this forum, this is the problem of "gaps and islands." My initial question gave me a link to this existing question . However, I believe that I have found a flaw in the existing question that I am trying to fix.

Given this data and the query from the existing question ...

declare @table table ( [Name] varchar(20) not null, [Pay] int not null, [Date] date not null ); insert into @table values ('Sally', 10, '08/28/2012') ,('Sally', 12, '09/06/2012') ,('Sally', 10, '05/17/2014') ,('Sally', 12, '01/01/2015') ,('Sally', 13, '01/01/2016'); WITH T1 AS (SELECT *, ROW_NUMBER() OVER ( PARTITION BY [NAME] ORDER BY DATE) - ROW_NUMBER() OVER ( PARTITION BY [NAME], [PAY] ORDER BY DATE) AS [Grp] FROM @table), T2 AS (SELECT *, MIN([DATE]) OVER ( PARTITION BY [NAME], [Grp]) AS [MinDate] FROM T1) SELECT [NAME], [PAY], [DATE], DENSE_RANK() OVER ( PARTITION BY [NAME] ORDER BY [MinDate]) AS CHANGEGROUP FROM T2 ORDER BY [NAME], [MinDate] 

I would expect to get this result ...

However, I get this ...

So, he thinks that the sequential lines Pay = 10 and Pay = 12 are on the same island, which is incorrect. Any ideas on how to fix this?

Thanks!

Yang

Answer 1

Another way to do this is in addition to the approach suggested by @MartinSmith. Use lag to get the previous “Payment” value and start a new group if it differs from the current “Payment” amount in the current amount.

 select name,pay,date ,sum(case when prev_pay=pay then 0 else 1 end) over(partition by name order by date) as change_group from (select t.*,lag(pay) over(partition by name order by date) as prev_pay FROM @table t ) t 

Your request from the previous question will become

 select id,plancode,begindate,enddate ,sum(case when prev_plan=plancode then 0 else 1 end) over(order by begindate desc) as grp from (select e.*,lag(plancode) over(order by begindate desc) AS prev_plan from @enrollments e ) t