SQL Server : find break in dates to show unique rows -

i have developed solution problem (i think), , keen see if there better way around this, can't feel there better way.

the problem: company name, , move in date shown. company leave, company come in , original company come back. make problem bit tricky, there may rogue dates company in there. best way explain via table:

table example

what need extract, first time company moved in, until broken different company , on.

the code have is:

if object_id('tempdb..#tmpdata') not null     drop table #tmpdata go  create table #tmpdata (     company_name nvarchar(30),     date_moved_in datetime,     id int identity(1,1),     unique_id int )  insert #tmpdata(company_name, date_moved_in)     select 'abc ltd','01/01/2017' union     select 'abc ltd','01/04/2017' union     select 'xyz ltd','01/10/2017' union     select 'abc ltd','01/12/2017';  declare @intminid int,         @intmaxid int,         @strnextcomp nvarchar(50),         @strcurrentcomp nvarchar(50),         @strpreviouscomp nvarchar(50),         @intmaxuid int;  select       @intminid = min(td.id),     @intmaxid = max(td.id)         #tmpdata td  update td set td.unique_id = 1 #tmpdata td td.id = @intminid;  while @intminid <= @intmaxid begin     select           @strcurrentcomp = td.company_name                 #tmpdata td                td.id = @intminid;      select           @strnextcomp = td.company_name                 #tmpdata td                td.id = (@intminid + 1)      select           @strpreviouscomp = case when exists (select 1                                              #tmpdata td                                              td.id = (@intminid - 1))                                     td.company_name                                     else 'no company exists'                            end                 #tmpdata td                td.id = (@intminid - 1)      select           @intmaxuid = max(td.unique_id)                 #tmpdata td      if(@strpreviouscomp null)         print 'nothing do'     else if((@strcurrentcomp <> @strnextcomp) , (@strcurrentcomp = @strpreviouscomp))      begin            update td         set td.unique_id = @intmaxuid         #tmpdata td         td.id = @intminid;     end     else     begin         update td         set td.unique_id = @intmaxuid + 1         #tmpdata td         td.id = @intminid;     end       set @intminid = @intminid + 1; end  select        company_name, min(date_moved_in) date_moved_in     #tmpdata group      company_name, unique_id order      unique_id asc 

any suggestions on how in more efficient way, or if errors spotted, feedback appreciated.

thanks,

leo

lag() should it...

with cte ( select company_name, date_moved_in, lag(company_name) on (order date_moved_in) prevcomp #temptable ) select company_name, date_moved_in cte prevcomp <> company_name or prevcomp null