Get all Valid time slices in a Table

Question

I have a table in which the entries are historized with SCD 2 Now I am looking for a possibility to get Alle PK which are valid at the same time:

For example my table Look like this:

PK;ValidFrom;ValidTo
635582110901;2016-01-04;2016-01-21
635582110901;2016-01-22;2016-01-26
635582110901;2016-01-27;2016-02-14
635582110901;2016-02-15;2016-11-10
**635582110901;2016-11-11;2017-01-23**
**635582110901;2016-11-16;2016-12-12**
635582110901;2016-12-13;2017-01-18
635582110901;2017-01-19;2017-01-22
635582110901;2017-01-23;2017-01-23
635582110901;2017-01-24;2017-02-21
635582110901;2017-02-22;9999-12-31

The select should give me the two bold Rows

thanks for your help

Answer 1

You can use exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.validfrom < t.validto and
                    t2.validto > t.validfrom and
                    t2.pk = t.pk and
                    t2.validfrom <> t.validfrom and
                    t2.validto <> t.validto
             );