Combinación de dos tablas SQL con diferentes campos y teniendo en cuenta fechas

Below is a query. Note also that I have added CREATE TABLE statements tables for your tables as well as INSERT statements with the sample data. We very much appreciate if you do this yourself, so that we, who are helping you in our free time, do not have to spend time on this. This time the amount of data was small, so I created this myself.

I also added a few more rows to the PercentHistory table, as I found it a little thin on test data.

Your description gave me some ideas of what the business rules are, but I had to make some extrapolations based on what I think is reasonable.

The solution uses a table of dates. You can learn about this concept in a short story on my web site, where you also find the script to create it: https://www.sommarskog.se/Short%20Stories/table-of-numbers.html#Dates Note that you will need to adjust the script, since you appears to havde dates before 1990.

The idea we use this table to produce a row for every day the employee has been employed. Then we retrieve the most recent start date for some disability and the most recent start date for a degree. When we compute two row_numbers. One is simply the date number for this employee's stint at the company, the other is the row number for a certain combo of disability and degree. The difference between these two numbers are the same as long as there are no changes. Then we can use MIN and MAX on the calendar date to get the start and end of the intervals.

 CREATE TABLE DisabilityTypeHistory(
       empid int NOT NULL,
       startcompanydate date NOT NULL,
       endcompanydate   date NULL,
       startdate        date NOT NULL,
       enddate          date NULL, 
       emptype          varchar(40) NULL, 
       PRIMARY KEY (empid, startcompanydate)
)
INSERT DisabilityTypeHistory 
   VALUES(1, '19800701', '20221228', '19800701', '20221228', 'No ailment'),
         (1, '20230201', NULL, '20230701',NULL, 'PDI')
go
CREATE TABLE DisabilityPercentHistory(
     empid int NOT NULL,
     startdate date NOT NULL,
     degree    int  NOT NULL,
     PRIMARY KEY (empid, startdate)
)
INSERT DisabilityPercentHistory
   VALUES(1, '20201111', 47),
         (1, '20220109', 33),
         (1, '20220117', 47),
         (1, '20230201', 65)

CREATE TABLE Employees (empid int NOT NULL PRIMARY KEY)
INSERT Employees(empid)
   VALUES(1)
go
; WITH DimSpan AS (
   SELECT TH.empid, TH.startcompanydate, TH.endcompanydate, d.d AS CalDate, 
          emptype = THsub.emptype, degree = isnull(PH.degree, 100), 
          grpno = row_number() OVER(PARTITION BY TH.empid, TH.startcompanydate ORDER BY d.d) -
                  row_number() OVER(PARTITION BY TH.empid, TH.startcompanydate, THsub.emptype, PH.degree ORDER BY d.d)
   FROM   (SELECT DISTINCT empid, startcompanydate, endcompanydate
           FROM   DisabilityTypeHistory) AS TH
   JOIN   Dates d ON d.d BETWEEN TH.startcompanydate AND isnull(TH.endcompanydate, dateadd(DAY, 1, convert(date, sysdatetime())))
   OUTER  APPLY (SELECT TOP (1) PH.degree
                 FROM   DisabilityPercentHistory PH
                 WHERE  PH.empid = TH.empid
                   AND  PH.startdate <= d.d
                 ORDER  BY PH.startdate DESC) AS PH
   OUTER APPLY (SELECT TOP (1) THsub.startdate, THsub.emptype
                FROM   DisabilityTypeHistory THsub
                WHERE  THsub.empid = TH.empid
                  AND  THsub.startcompanydate = TH.startcompanydate
                  AND  THsub.startdate <= d.d
                ORDER  BY THsub.startdate DESC) AS THsub
)
SELECT empid, startcompanydate, endcompanydate, MIN(CalDate), 
       nullif(MAX(CalDate), dateadd(DAY, 1, convert(date, sysdatetime()))), 
       emptype, degree, grpno
FROM   DimSpan 
GROUP  BY empid, startcompanydate, endcompanydate, emptype, degree, grpno
ORDER  BY empid, startcompanydate, MIN(CalDate)



SELECT * FROM DisabilityTypeHistory
SELECT * FROM DisabilityPercentHistory
go
DROP TABLE DisabilityPercentHistory
DROP TABLE DisabilityTypeHistory
DROP TABLE Employees