sql - Ermitteln von unterschiedlichen Datensätzen

IF OBJECT_ID('tempdb..#sourcetable') IS NOT NULL DROP Table #sourcetable
IF OBJECT_ID('tempdb..#tmptable') IS NOT NULL DROP Table #tmptable
IF OBJECT_ID('tempdb..#result') IS NOT NULL DROP Table #result
IF OBJECT_ID('tempdb..#result_tmp') IS NOT NULL DROP Table #result_tmp


create table #sourcetable 
	(
	ID int,
	Wert1 nvarchar(10),
	Wert2 nvarchar(10)
	)

create table #tmptable 
	(
	RowNo int,
	ID int,
	Wert1 nvarchar(10),
	Wert2 nvarchar(10)
	)

create table #result_tmp
	(
	ID int
	)

create table #result
	(
	EntityID int,
	ID int,
	State_Change_Wert1 int,
	State_Change_Wert2 int
	)


INSERT INTO #sourcetable 
SELECT 1,'x','t' UNION ALL
SELECT 1,'x',NULL UNION ALL
SELECT 1,'y','b' UNION ALL
SELECT 2,NULL,'c' UNION ALL
SELECT 2,NULL,NULL UNION ALL
SELECT 4,'x','t' UNION ALL
SELECT 4,'x',NULL UNION ALL
SELECT 4,'y','b' 


 select * from #sourcetable


insert into #tmptable (RowNo,ID,Wert1,Wert2)
select row_number() over(order by id),ID,Wert1,Wert2
from #sourcetable




 

update #tmptable set Wert1 = 'UNGUELTIG' where Wert1 is NULL
update #tmptable set Wert2 = 'UNGUELTIG' where Wert2 is NULL


insert into #result_tmp (ID)
select distinct ID
from #sourcetable

insert into #result (EntityID,ID)
select row_number() over(order by id),ID
from #result_tmp


DECLARE @i INT;
DECLARE @max INT;
DECLARE @tmp int;
DECLARE @changes_w1 INT;
DECLARE @changes_w2 INT;
DECLARE  @laststate_w1 nvarchar(10);
DECLARE  @laststate_w2 nvarchar(10);
DECLARE @i_result int;
DECLARE @max_result int;
set @max = (select max(RowNo) from #tmptable);
set @i_result = 1;
set @max_result = (select max(EntityID) from #result);


WHILE @i_result <= @max_result
begin


set @tmp = (select ID from #result where @i_result = EntityID);

set @laststate_w1 = (select wert1 from #tmptable where RowNo in (select min(RowNo) from #tmptable where ID = @tmp));
set @laststate_w2 = (select wert2 from #tmptable where RowNo in (select min(RowNo) from #tmptable where ID = @tmp));
SET @i = (select min(RowNo) from #tmptable where ID = @tmp) + 1;
set @changes_w1 = 0;
set @changes_w2 = 0;



WHILE @i <= @max
BEGIN
	if @tmp = (select ID from #tmptable where RowNo = @i)
	BEGIN
	IF @laststate_w1 <> (select Wert1 from #tmptable where RowNo = @i)
	BEGIN
		set	@laststate_w1 = (select Wert1 from #tmptable where RowNo = @i);
		set @changes_w1 = @changes_w1 + 1;
	END

	IF @laststate_w2 <> (select Wert2 from #tmptable where RowNo = @i)
	BEGIN
		set	@laststate_w2 = (select Wert2 from #tmptable where RowNo = @i);
		set @changes_w2 = @changes_w2 + 1;
	END

	
	END


   SET @i = @i + 1;
END;

update #result set State_Change_Wert1 = @changes_w1 where EntityId =  @i_result;
update #result set State_Change_Wert2 = @changes_w2 where EntityId =  @i_result;

set @i_result = @i_result + 1;
end --result lopp



select * from #result