SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Foo(
    FooId INT NOT NULL,
    ValidFrom DATE/*TIME*/ NOT NULL,
    ValidUntil DATE/*TIME*/ NOT NULL
)

CREATE TABLE FooBar (
    FooId INT NOT NULL,
    BarId INT NOT NULL,
    ValidFrom DATE/*TIME*/ NOT NULL,
    ValidUntil DATE/*TIME*/ NOT NULL,
    Priority TINYINT NOT NULL,
    SetId nvarchar(5)
)

INSERT INTO Foo(FooId, ValidFrom, ValidUntil)
VALUES 
  (1, '2020-01-01', '2021-12-31')
, (2, '2020-01-01', '2021-06-30')

INSERT INTO FooBar(FooId, BarId, ValidFrom, ValidUntil, Priority, SetId)
VALUES 
-- First set for FooId = 1 with prio 1
  (1, 1, '2021-01-01', '2021-03-01', 1, 'Set 1')
, (1, 2, '2021-01-01', '2021-03-01', 1, 'Set 1')
, (1, 3, '2021-01-01', '2021-03-01', 1, 'Set 1')
-- Second set for FooId = 1 with prio 2 
, (1, 1, '2021-02-01', '2021-06-01', 2, 'Set 2')
, (1, 2, '2021-02-01', '2021-06-01', 2, 'Set 2')
-- Third set for FooId = 1 with prio 3
, (1, 1, '2021-01-01', '2021-12-31', 3, 'Set 3')
, (1, 2, '2021-01-01', '2021-12-31', 3, 'Set 3')
, (1, 3, '2021-01-01', '2021-12-31', 3, 'Set 3')
-- Fourth set for FooId = 1 with Prio 1
, (1, 4, '2021-04-01', '2021-04-02', 1, 'Set 4')
, (1, 5, '2021-04-01', '2021-04-02', 1, 'Set 4')
-- First set for FooId = 2 with prio 3
, (2, 6, '2021-01-01', '2021-04-02', 3, 'Set 1')

select * from Foo

select * from FooBar


FooId
ValidFrom
ValidUntil


1
2020-01-01
2021-12-31

2
2020-01-01
2021-06-30




FooId
BarId
ValidFrom
ValidUntil
Priority
SetId


1
1
2021-01-01
2021-03-01
1
Set 1

1
2
2021-01-01
2021-03-01
1
Set 1

1
3
2021-01-01
2021-03-01
1
Set 1

1
1
2021-02-01
2021-06-01
2
Set 2

1
2
2021-02-01
2021-06-01
2
Set 2

1
1
2021-01-01
2021-12-31
3
Set 3

1
2
2021-01-01
2021-12-31
3
Set 3

1
3
2021-01-01
2021-12-31
3
Set 3

1
4
2021-04-01
2021-04-02
1
Set 4

1
5
2021-04-01
2021-04-02
1
Set 4

2
6
2021-01-01
2021-04-02
3
Set 1


-- FooBar determines periods
with ValidFrom as
(
  select f.FooId,
         f.ValidFrom
  from Foo f
    union
  select f.FooId,
         dateadd(day, 1, f.ValidUntil)
  from Foo f
    union
  select fb.FooId,
         fb.ValidFrom
  from FooBar fb
    union
  select fb.FooId,
         dateadd(day, 1, fb.ValidUntil)
  from Foobar fb
)
select vf.FooId,
       vf.ValidFrom,
       dateadd(day, -1, lead(vf.ValidFrom) over(partition by vf.FooId order by vf.ValidFrom)) as ValidUntil
from ValidFrom vf
order by vf.FooId,
         vf.ValidFrom;


FooId
ValidFrom
ValidUntil


1
2020-01-01
2020-12-31

1
2021-01-01
2021-01-31

1
2021-02-01
2021-03-01

1
2021-03-02
2021-03-31

1
2021-04-01
2021-04-02

1
2021-04-03
2021-06-01

1
2021-06-02
2021-12-31

1
2022-01-01
null

2
2020-01-01
2020-12-31

2
2021-01-01
2021-04-02

2
2021-04-03
2021-06-30

2
2021-07-01
null


-- remove last period for each FooId
with ValidFrom as
(
  select f.FooId,
         f.ValidFrom
  from Foo f
    union
  select f.FooId,
         dateadd(day, 1, f.ValidUntil)
  from Foo f
    union
  select fb.FooId,
         fb.ValidFrom
  from FooBar fb
    union
  select fb.FooId,
         dateadd(day, 1, fb.ValidUntil)
  from Foobar fb
),
ValidPeriod as
(
  select vf.FooId,
         vf.ValidFrom,
         dateadd(day, -1, lead(vf.ValidFrom) over(partition by vf.FooId order by vf.ValidFrom)) as ValidUntil
  from ValidFrom vf
)
select vp.FooId,
       vp.ValidFrom,
       vp.ValidUntil
from ValidPeriod vp
where exists ( select 'x'
               from ValidPeriod vp2
               where vp2.FooId = vp.FooId
                 and vp2.ValidFrom > vp.ValidFrom )
order by vp.FooId,
         vp.ValidFrom;


FooId
ValidFrom
ValidUntil


1
2020-01-01
2020-12-31

1
2021-01-01
2021-01-31

1
2021-02-01
2021-03-01

1
2021-03-02
2021-03-31

1
2021-04-01
2021-04-02

1
2021-04-03
2021-06-01

1
2021-06-02
2021-12-31

2
2020-01-01
2020-12-31

2
2021-01-01
2021-04-02

2
2021-04-03
2021-06-30


-- join with FooBar
with ValidFrom as
(
  select f.FooId,
         f.ValidFrom
  from Foo f
    union
  select f.FooId,
         dateadd(day, 1, f.ValidUntil)
  from Foo f
    union
  select fb.FooId,
         fb.ValidFrom
  from FooBar fb
    union
  select fb.FooId,
         dateadd(day, 1, fb.ValidUntil)
  from Foobar fb
),
ValidPeriod as
(
  select vf.FooId,
         vf.ValidFrom,
         dateadd(day, -1, lead(vf.ValidFrom) over(partition by vf.FooId order by vf.ValidFrom)) as ValidUntil
  from ValidFrom vf
)
select vp.FooId,
       fb.BarId,
       vp.ValidFrom,
       vp.ValidUntil,
     --fb.ValidFrom,
     --fb.ValidUntil,
     --fb.Priority,
       fb.SetId
from ValidPeriod vp
left join FooBar fb
  on  fb.FooId = vp.FooId
  and fb.ValidFrom <= vp.ValidUntil
  and fb.ValidUntil >= vp.ValidFrom
  and not exists ( select 'x'
                   from FooBar fb2
                   where fb2.FooId = fb.FooId
                     and fb2.BarId = fb.BarId
                     and fb2.ValidFrom <= vp.ValidUntil
                     and fb2.ValidUntil >= vp.ValidFrom
                     and fb2.Priority < fb.Priority )
where exists ( select 'x'
               from ValidPeriod vp2
               where vp2.FooId = vp.FooId
                 and vp2.ValidFrom > vp.ValidFrom )
order by vp.FooId,
         vp.ValidFrom,
         fb.BarId;


FooId
BarId
ValidFrom
ValidUntil
SetId


1
null
2020-01-01
2020-12-31
null

1
1
2021-01-01
2021-01-31
Set 1

1
2
2021-01-01
2021-01-31
Set 1

1
3
2021-01-01
2021-01-31
Set 1

1
1
2021-02-01
2021-03-01
Set 1

1
2
2021-02-01
2021-03-01
Set 1

1
3
2021-02-01
2021-03-01
Set 1

1
1
2021-03-02
2021-03-31
Set 2

1
2
2021-03-02
2021-03-31
Set 2

1
3
2021-03-02
2021-03-31
Set 3

1
1
2021-04-01
2021-04-02
Set 2

1
2
2021-04-01
2021-04-02
Set 2

1
3
2021-04-01
2021-04-02
Set 3

1
4
2021-04-01
2021-04-02
Set 4

1
5
2021-04-01
2021-04-02
Set 4

1
1
2021-04-03
2021-06-01
Set 2

1
2
2021-04-03
2021-06-01
Set 2

1
3
2021-04-03
2021-06-01
Set 3

1
1
2021-06-02
2021-12-31
Set 3

1
2
2021-06-02
2021-12-31
Set 3

1
3
2021-06-02
2021-12-31
Set 3

2
null
2020-01-01
2020-12-31
null

2
6
2021-01-01
2021-04-02
Set 1

2
null
2021-04-03
2021-06-30
null

FooId	BarId	ValidFrom	ValidUntil	Priority	SetId
1	1	2021-01-01	2021-03-01	1	Set 1
1	2	2021-01-01	2021-03-01	1	Set 1
1	3	2021-01-01	2021-03-01	1	Set 1
1	1	2021-02-01	2021-06-01	2	Set 2
1	2	2021-02-01	2021-06-01	2	Set 2
1	1	2021-01-01	2021-12-31	3	Set 3
1	2	2021-01-01	2021-12-31	3	Set 3
1	3	2021-01-01	2021-12-31	3	Set 3
1	4	2021-04-01	2021-04-02	1	Set 4
1	5	2021-04-01	2021-04-02	1	Set 4
2	6	2021-01-01	2021-04-02	3	Set 1

FooId	ValidFrom	ValidUntil
1	2020-01-01	2020-12-31
1	2021-01-01	2021-01-31
1	2021-02-01	2021-03-01
1	2021-03-02	2021-03-31
1	2021-04-01	2021-04-02
1	2021-04-03	2021-06-01
1	2021-06-02	2021-12-31
1	2022-01-01	null
2	2020-01-01	2020-12-31
2	2021-01-01	2021-04-02
2	2021-04-03	2021-06-30
2	2021-07-01	null