The following might get you in the right direction, but you have to update/delete/insert the rows into your table yourself:
create table #yourtable1(
id int
,a int
,b int
,c int
,d int
,e int
,fromdate int
,todate int
);
insert into #yourtable1 values
/*****
* Your sample data untouched
*/
(1,1,2,6,4,9,1900,1950)
,(1,2,3,6,8,3,1951,2000)
,(1,3,6,6,1,1,2001,9998)
/*****
* Your sample data untouched
*/
,(2,1,2,6,4,9,1900,1950)
,(2,2,3,6,8,3,1951,2000)
,(2,3,6,6,1,1,2001,9998)
/*****
* Modified sample data with gap
*/
,(3,1,2,6,4,9,1900,1920)
,(3,2,3,6,8,3,1951,2000)
,(3,3,6,6,1,1,2001,9998)
;
create table #yourtable2(
id int
,attribute char(1)
,value int
,fromdate int
,todate int
);
insert into #yourtable2 values
/*****
* Your sample data untouched
*/
(1,'B',10,1900,1930)
,(1,'B',4,1931,2050)
,(1,'B',7,2051,9998)
/*****
* Modified sample data to test other combinations, amongst modifying multiple columns
*/
,(2,'B',10,1900,1930)
,(2,'B',4,1931,2050)
,(2,'B',7,2051,2070)
,(2,'B',7,2101,9998)
,(2,'C',10,1900,1930)
/*****
* Your sample data untouched to be applied on "gap" data
*/
,(3,'B',10,1900,1930)
,(3,'B',4,1931,2050)
,(3,'B',7,2051,9998)
;
with cte_tally(n)
as (select * from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t(n))
/*****
* Split every record into atoms (one row per year)
*/
,cte_split(id,a,b,c,d,e,y)
as (select a.id
,max(case
when d.attribute is not null
and d.attribute='A'
then d.value
else isnull(c.a,-999999)
end) as a
,max(case
when d.attribute is not null
and d.attribute='B'
then d.value
else isnull(c.b,-999999)
end) as b
,max(case
when d.attribute is not null
and d.attribute='C'
then d.value
else isnull(c.c,-999999)
end) as c
,max(case
when d.attribute is not null
and d.attribute='D'
then d.value
else isnull(c.d,-999999)
end) as d
,max(case
when d.attribute is not null
and d.attribute='E'
then d.value
else isnull(c.e,-999999)
end) as e
,b.y
from (select id
,min(fromdate) as fromdate
,max(todate) as todate
from #yourtable1
group by id
) as a
inner join (select row_number() over(order by (select null)) as y
from cte_tally as a
cross apply cte_tally as b
cross apply cte_tally as c
cross apply cte_tally as d
) as b
on b.y>=a.fromdate
and b.y<=a.todate
left outer join #yourtable1 as c
on c.id=a.id
and c.fromdate<=b.y
and c.todate>=b.y
left outer join #yourtable2 as d
on d.id=a.id
and d.fromdate<=b.y
and d.todate>=b.y
group by a.id
,b.y
)
select *
into #temptemp
from cte_split
;
/*****
* Collect/find fromdate(s)
*/
with cte_collect_from(id,a,b,c,d,e,fromdate,rn)
as (select a.id
,a.a
,a.b
,a.c
,a.d
,a.e
,a.y as fromdate
,row_number() over(partition by a.id order by a.y) as rn
from #temptemp as a
left outer join #temptemp as b
on b.id=a.id
and b.a=a.a
and b.b=a.b
and b.c=a.c
and b.d=a.d
and b.e=a.e
and b.y=a.y-1
where b.id is null
)
/*****
* Collect/find todate(s)
*/
,cte_collect_to(id,a,b,c,d,e,todate,rn)
as (select a.id
,a.a
,a.b
,a.c
,a.d
,a.e
,a.y as todate
,row_number() over(partition by a.id order by a.y) as rn
from #temptemp as a
left outer join #temptemp as b
on b.id=a.id
and b.a=a.a
and b.b=a.b
and b.c=a.c
and b.d=a.d
and b.e=a.e
and b.y=a.y+1
where b.id is null
)
/*****
* Combine fromdate(s) and todate(s)
*/
select a.id
,a.a
,a.b
,a.c
,a.d
,a.e
,a.fromdate
,b.todate
from cte_collect_from as a
left outer join cte_collect_to as b
on b.id=a.id
and b.rn=a.rn
order by a.id
,a.fromdate
;
drop table #temptemp;
drop table #yourtable2;
drop table #yourtable1;