SQLite 3.16

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

create table person (
  p_id  integer not null primary key,
  nname text    not null,
  vname text    not null,
  constraint unq_nname_vname unique (nname,vname) -- um Dppelungen zu vermeiden
);
insert into person (nname, vname)
values ('Müller', 'Christian'), ('Huber', 'Martin');
create table termin (
  p_id  integer    not null references person(p_id),
  modul varchar(1) not null check (modul in ('A', 'B', 'C', 'D')),
  datum date       not null,
  constraint pk_termin primary key (p_id, modul)
);
insert into termin (p_id, modul, datum)
values (1, 'A', '2012-01-01'), (1, 'B', '2014-05-12'), (1, 'D', '2018-06-22'),
       (2, 'A', '2018-01-01'), (2, 'C', '2014-06-13');
-- jüngstes Datum je Person ermitteln
create view v_max_datum_person as
  select p_id,
         max(datum) as mx_datum
  from   termin
  group  by p_id;
--als Zwischenergebnis anzeigen
select * from v_max_datum_person;


p_id
mx_datum


1
2018-06-22

2
2018-01-01


select p.p_id,
       nname,
       vname,
       modul,
       datum,
       date(datum, '+2 years') as datum2
from   person as p
       inner join termin as t
               on p.p_id = t.p_id
       inner join v_max_datum_person as v
               on v.p_id = t.p_id and
                  v.mx_datum = t.datum;


p_id
nname
vname
modul
datum
datum2


1
Müller
Christian
D
2018-06-22
2020-06-22

2
Huber
Martin
A
2018-01-01
2020-01-01


--oder auch so
select p.p_id,
       nname,
       vname,
       modul,
       datum,
       date(mx_datum, '+2 years') as datum2
from   person as p
       inner join termin as t
               on p.p_id = t.p_id
       left join v_max_datum_person as v
              on v.p_id = t.p_id and
                 v.mx_datum = t.datum;


p_id
nname
vname
modul
datum
datum2


1
Müller
Christian
A
2012-01-01
null

1
Müller
Christian
B
2014-05-12
null

1
Müller
Christian
D
2018-06-22
2020-06-22

2
Huber
Martin
A
2018-01-01
2020-01-01

2
Huber
Martin
C
2014-06-13
null


create view termin_neu as
  select *
  from   termin
  union all
  select p_id,
         'NEU',
         date(mx_datum, '+2 years')
  from   v_max_datum_person;
select * from termin_neu
order by 1,2;


p_id
modul
datum


1
A
2012-01-01

1
B
2014-05-12

1
D
2018-06-22

1
NEU
2020-06-22

2
A
2018-01-01

2
C
2014-06-13

2
NEU
2020-01-01


--als Kreuztabelle
select p.p_id,
       min(nname || ', ' || vname) as person,
       min(case when modul = 'A' then datum else null end) as A,
       min(case when modul = 'B' then datum else null end) as B,
       min(case when modul = 'C' then datum else null end) as C,
       min(case when modul = 'D' then datum else null end) as D,
       min(case when modul = 'NEU' then datum else null end) as NEU
from   person as p
       inner join termin_neu as t
               on p.p_id = t.p_id
group  by p.p_id;


p_id
person
A
B
C
D
NEU


1
Müller, Christian
2012-01-01
2014-05-12
null
2018-06-22
2020-06-22

2
Huber, Martin
2018-01-01
null
2014-06-13
null
2020-01-01

p_id	nname	vname	modul	datum	datum2
1	Müller	Christian	D	2018-06-22	2020-06-22
2	Huber	Martin	A	2018-01-01	2020-01-01

p_id	nname	vname	modul	datum	datum2
1	Müller	Christian	A	2012-01-01	null
1	Müller	Christian	B	2014-05-12	null
1	Müller	Christian	D	2018-06-22	2020-06-22
2	Huber	Martin	A	2018-01-01	2020-01-01
2	Huber	Martin	C	2014-06-13	null

p_id	person	A	B	C	D	NEU
1	Müller, Christian	2012-01-01	2014-05-12	null	2018-06-22	2020-06-22
2	Huber, Martin	2018-01-01	null	2014-06-13	null	2020-01-01