Consolidate information (time serie) from two tables

MS SQL Server

I have two tables with different accounts from the same customer:

Table1:

ID	ACCOUNT	FROM	TO
1	A	01.10.2019	01.12.2019
1	A	01.02.2020	09.09.9999

and table2:

ID	ACCOUNT	FROM	TO
1	B	01.12.2019	01.01.2020

As result I want a table that summarize the story of this costumer and shows when he had an active account and when he doesn’t.

Result:

ID	FROM	TO	ACTIV Y/N
1	01.10.2019	01.01.2020	Y
1	02.01.2020	31.01.2020	N
1	01.02.2020	09.09.9999	Y

Can someone help me with some ideas how to proceed?

Answer

This is the typical gaps and island problem, and it’s not usually easy to solve.

You can achieve your goal using this query, I will explain it a little bit.

You can test on this db<>fiddle.

First of all… I have unified your two tables into one to simplify the query.

-- ##table1
select 1 as ID, 'A' as ACCOUNT, convert(date,'2019-10-01') as F, convert(date,'2019-12-01') as T into ##table1
union all
select 1 as ID, 'A' as ACCOUNT, convert(date,'2020-02-01') as F, convert(date,'9999-09-09') as T

-- ##table2
select 1 as ID, 'B' as ACCOUNT, convert(date,'2019-12-01') as F, convert(date,'2020-01-01') as T into ##table2

-- ##table3
select * into ##table3 from ##table1 union all select * from ##table2

You can then get your gaps and island using, for example, a query like this. It combines recursive cte to generate a calendar (cte_cal) and lag and lead operations to get the previous/next record information to build the gaps.

with 
cte_cal as (
  select min(F) as D from ##table3 
  union all
  select dateadd(day,1,D) from cte_cal where d < = '2021-01-01'
),
table4 as (
  select t1.ID, t1.ACCOUNT, t1.F, isnull(t2.T, t1.T) as T, lag(t2.F, 1,null) over (order by  t1.F) as SUP
  from ##table3 t1
    left join ##table3 t2
      on t1.T=t2.F
)
select 
  ID,
  case when T = D then F else D end as "FROM",
  isnull(dateadd(day,-1,lead(D,1,null) over (order by D)),'9999-09-09') as "TO",
  case when case when T = D then F else D end = F then 'Y' else 'N' end as "ACTIV Y/N"
from (
  select *
  from cte_cal c 
    cross apply (
      select t.*
      from table4 t
      where t.SUP is null 
        and (
          c.D = t or 
          c.D = dateadd(day,1,t.T)    
        )
      ) t
  union all
  select F, * from table4 where T = '9999-09-09'
  ) p
order by 1
option (maxrecursion 0)

Dates like '9999-09-09' must be treated like exceptions, otherwise I would have to create a calendar until that date, so the query would take long time to resolve.