SQL Server 2019

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

CREATE TABLE dbo.History
(
  Id          int, 
  ServiceDate date, 
  Hours       int, 
  ClientId    int, 
  ClientName  sysname
);

INSERT dbo.History(Id, ServiceDate, Hours, ClientId, ClientName)
VALUES(1,'20211015',3,123,N'Tom Holland'),
      (2,'20211025',5,123,N'Tom Holland'),
      (3,'20220114',2,123,N'Tom Holland');

CREATE TABLE dbo.Responsible
(
  ClientId             int, 
  ClientName           sysname, 
  ResponsibleId        int, 
  ResponsibleName      sysname, 
  ResponsibleStartDate date, 
  ResponsibleEndtDate  date
);

INSERT dbo.Responsible(ClientId, ClientName, ResponsibleId, 
  ResponsibleName, ResponsibleStartDate, ResponsibleEndtDate)
VALUES
(123,N'Tom Holland',77,N'Thomas Anderson', '20200917','20211017'),
(123,N'Tom Holland',88,N'Tom Cruise',      '20211018','29991231'),
(123,N'Tom Holland',99,N'Sten Lee', 	   '20220107','29991231');
6 rows affected

/*
1) If Service Date falls in multiple date ranges from Responsible Table, 
Responsible Should be the person who's ResponsibleStartDate is closer 
to the ServiceDate:

Id	ServiceDate	Hours	ClientId	ClientName	ResponsibleName
1	2021-10-15	3	123	Tom Holland	Thomas Anderson
2	2021-10-25	5	123	Tom Holland	Tom Cruise
3	2022-01-14	2	123	Tom Holland	Sten Lee
*/

;WITH cte AS 
(
  SELECT h.Id, 
       h.ServiceDate, 
       h.Hours, 
       h.ClientId, 
       h.ClientName, 
       r.ResponsibleName, 
       RankOrderedByProximityToServiceDate = ROW_NUMBER() OVER 
         (PARTITION BY h.Id 
          ORDER BY ABS(DATEDIFF(DAY, ResponsibleStartDate, ServiceDate)))
  FROM dbo.History AS h
  LEFT JOIN dbo.Responsible AS r
     ON (h.ClientId = r.ClientId 
     AND h.ServiceDate BETWEEN r.ResponsibleStartDate AND r.ResponsibleEndtDate)
)
SELECT Id, ServiceDate, Hours, ClientId, ClientName, ResponsibleName
FROM cte WHERE RankOrderedByProximityToServiceDate = 1;


Id
ServiceDate
Hours
ClientId
ClientName
ResponsibleName


1
2021-10-15
3
123
Tom Holland
Thomas Anderson

2
2021-10-25
5
123
Tom Holland
Tom Cruise

3
2022-01-14
2
123
Tom Holland
Sten Lee


/*
2) Keep all rows, if Service Date falls in multiple date ranges from 
Responsible Table, but split Hours evenly between Responsible:

Id	ServiceDate	Hours	ClientId	ClientName	ResponsibleName
1	2021-10-15	3	123	Tom Holland	Thomas Anderson
2	2021-10-25	5	123	Tom Holland	Tom Cruise
3	2022-01-14	1	123	Tom Holland	Tom Cruise
3	2022-01-14	1	123	Tom Holland	Sten Lee
*/

  SELECT h.Id, 
       h.ServiceDate,
       Hours = CONVERT(decimal(11,2), 
         h.Hours * 1.0
         / COUNT(h.Id) OVER (PARTITION BY h.Id)),
       h.ClientId, 
       h.ClientName, 
       r.ResponsibleName
  FROM dbo.History AS h
  LEFT JOIN dbo.Responsible AS r
     ON (h.ClientId = r.ClientId 
     AND h.ServiceDate BETWEEN r.ResponsibleStartDate AND r.ResponsibleEndtDate);


Id
ServiceDate
Hours
ClientId
ClientName
ResponsibleName


1
2021-10-15
3.00
123
Tom Holland
Thomas Anderson

2
2021-10-25
5.00
123
Tom Holland
Tom Cruise

3
2022-01-14
1.00
123
Tom Holland
Tom Cruise

3
2022-01-14
1.00
123
Tom Holland
Sten Lee

Id	ServiceDate	Hours	ClientId	ClientName	ResponsibleName
1	2021-10-15	3	123	Tom Holland	Thomas Anderson
2	2021-10-25	5	123	Tom Holland	Tom Cruise
3	2022-01-14	2	123	Tom Holland	Sten Lee

Id	ServiceDate	Hours	ClientId	ClientName	ResponsibleName
1	2021-10-15	3.00	123	Tom Holland	Thomas Anderson
2	2021-10-25	5.00	123	Tom Holland	Tom Cruise
3	2022-01-14	1.00	123	Tom Holland	Tom Cruise
3	2022-01-14	1.00	123	Tom Holland	Sten Lee