Я думаю, что это соединение по перекрытию диапазона дат, а затем условная логика:

select s.empl_id,
    s.scale_id,
    v.amount,
    case when s.date_from <= v.date_from then v.date_from else s.date_from end as date_from,
    case when v.date_to   <= s.date_from then v.date_to   else s.date_to   end as date_to
from scale_table s
inner join value_table v 
    on  s.scale_id = v.scale_id
    and s.date_from <= v.date_to
    and s.date_to   >= v.date_from

Демо на DB Fiddle - отфильтровано по сотруднику 187:

empl_id | scale_id | amount    | date_from  | date_to   
:------ | :------- | :-------- | :--------- | :---------
187     | B3EL9    | 78084.00  | 2014-03-01 | 2017-06-30
187     | B3EL9    | 81432.00  | 2015-07-01 | 2017-06-30
187     | B4EL6    | 81720.00  | 2017-07-01 | 2019-10-31
187     | B5EL9    | 100092.00 | 2019-11-01 | 2099-12-31

Это довольно классическая проблема перекрывающихся диапазонов дат.

Это отличный ответ, который охватывает его: Определите, перекрываются ли два диапазона дат

Для вашего кода это будет примерно так (см. внизу сообщения код, используемый для создания временных таблиц):

DECLARE @StartDate date = '2015-01-01',
        @EndDate date = '2020-12-14';

SELECT s.empl_id
     , s.scale_id
     , v.amount
     , StartDate    = IIF(x.EmpScaleStartDate < @StartDate, @StartDate, x.EmpScaleStartDate) -- Clamp the start date
     , EndDate      = x.EmpScaleEndDate
FROM #scale_table s
    JOIN #value_table v ON v.scale_id = s.scale_id
    CROSS APPLY (
        SELECT EmpScaleStartDate    = IIF(v.date_from <= s.date_from, s.date_from, v.date_from) -- Pick the valid start date
            ,  EmpScaleEndDate      = IIF(s.date_to   <= v.date_to  , s.date_to  , v.date_to) -- Pick the valid end date
    ) x
WHERE (s.date_from <= v.date_to) AND (s.date_to >= v.date_from) -- Do the two ranges overlap?
    AND (s.date_to >= @StartDate AND s.date_from <= @EndDate) -- Only look at records within our target range
ORDER BY s.empl_id, s.date_from

Для emp 187 это выведет:

| empl_id | scale_id | amount    | StartDate  | EndDate    | 
|---------|----------|-----------|------------|------------| 
| 187     | B3EL9    | 78084.00  | 2015-01-01 | 2015-06-30 | 
| 187     | B3EL9    | 81432.00  | 2015-07-01 | 2017-06-30 | 
| 187     | B4EL6    | 81720.00  | 2017-07-01 | 2019-10-31 | 
| 187     | B5EL9    | 100092.00 | 2019-11-01 | 2099-12-31 | 

Объяснение:

Вы в основном имеете дело с 3 отдельными диапазонами дат и хотите найти, где они все перекрываются.

Эти диапазоны дат:

• Дата начала/окончания суммы шкалы заработной платы
• Дата начала/окончания периода, в течение которого шкала заработной платы была назначена сотруднику
• Дата начала/окончания отчета

Первый шаг — захватить только действительные записи:

DECLARE @StartDate date = '2015-01-01', @EndDate date = '2020-12-14';
SELECT *
FROM #scale_table s
WHERE s.date_to >= @StartDate AND s.date_from <= @EndDate

Это говорит:

Убедитесь, что мы извлекаем записи о сотрудниках только в тех случаях, когда их даты шкалы находятся в интересующем нас диапазоне. Судя по предоставленным вами данным, это запись о каждом сотруднике.

Следующий:

Мы хотим присоединить #value_table к этим записям, чтобы мы могли видеть, были ли какие-либо изменения в каждой из этих шкал заработной платы за то время, пока они были.

DECLARE @StartDate date = '2015-01-01', @EndDate date = '2020-12-14';
SELECT *
FROM #scale_table s
    JOIN #value_table v ON v.scale_id = s.scale_id
WHERE (s.date_from <= v.date_to) AND (s.date_to >= v.date_from) -- Do the two ranges overlap?
    AND (s.date_to >= @StartDate AND s.date_from <= @EndDate) -- Only look at records within our target range

Теперь у нас есть набор данных, с которым мы можем поиграть. У нас есть запись о каждом сотруднике и истории его шкалы заработной платы, а также об изменениях самих шкал.

Теперь нам просто нужно выяснить, как получить правильные даты начала/окончания...

Следующий:

Вот где это происходит:

CROSS APPLY (
    SELECT EmpScaleStartDate    = IIF(v.date_from <= s.date_from, s.date_from, v.date_from)
        ,  EmpScaleEndDate      = IIF(v.date_to   >= s.date_to  , s.date_to  , v.date_to)
) x

Эта логика решает, какую дату начала или окончания отображать. Я использую только CROSS APPLY, чтобы было легче повторно использовать эту логику во всем запросе и сделать его немного более читабельным, чтобы у вас не было кучи вложенных функций в одной строке.

Если бы у 187 было B3EL9 с 2014-03-01 по 2017-06-30

А шкала заработной платы платила 78 084 доллара от 2013-01-01 до 2015-06-30.

Затем мы должны показать дату начала 2014-03-01 и дату окончания 2015-06-30 для этой строки.

Последний шаг:

Зажим на первом свидании. Зажим означает привязку одного значения в диапазоне двух других значений. Поскольку этот отчет выполняется на основе 2015-01-01 to Current. Мы хотим, чтобы диапазоны, начинающиеся в 2013 или 2014 году, отображались вместо 2015-01-01.

Это все, что делает:

SELECT StartDate = IIF(x.EmpScaleStartDate < @StartDate, @StartDate, x.EmpScaleStartDate)

Пример данных в форме SQL:

IF OBJECT_ID('tempdb..#scale_table','U') IS NOT NULL DROP TABLE #scale_table; --SELECT * FROM #scale_table
CREATE TABLE #scale_table (
    empl_id     int             NOT NULL,
    scale_id    varchar(100)    NOT NULL,
    date_from   date            NOT NULL,
    date_to     date            NOT NULL
);

INSERT INTO #scale_table (empl_id, scale_id, date_from, date_to)
VALUES (187,'B3EL9', '2014-03-01','2017-06-30')
    ,  (187,'B4EL6', '2017-07-01','2019-10-31')
    ,  (187,'B5EL9', '2019-11-01','2099-12-31')
    ,  (214,'M115' , '2006-10-01','2099-12-31')
    ,  (618,'B3L9' , '2014-01-01','2019-10-31')
    ,  (618,'B6L9' , '2019-11-01','2099-12-31');

IF OBJECT_ID('tempdb..#value_table','U') IS NOT NULL DROP TABLE #value_table; --SELECT * FROM #value_table
CREATE TABLE #value_table (
    scale_id    varchar(100)    NOT NULL,
    amount      decimal(10, 2)  NOT NULL,
    date_from   date            NOT NULL,
    date_to     date            NOT NULL
);

INSERT INTO #value_table (scale_id, amount, date_from, date_to)
VALUES ('B3EL9',78084.00  ,'2013-01-01', '2015-06-30')
    ,  ('B3EL9',81432.00  ,'2015-07-01', '2099-12-31')
    ,  ('B4EL6',78348.00  ,'2013-01-01', '2015-06-30')
    ,  ('B4EL6',81720.00  ,'2015-07-01', '2099-12-31')
    ,  ('B5EL9',95964.00  ,'2013-01-01', '2015-06-30')
    ,  ('B5EL9',100092.00 ,'2015-07-01', '2099-12-31')
    ,  ('B3L9 ',52728.00  ,'2013-01-01', '2015-08-15')
    ,  ('B3L9 ',54996.00  ,'2015-08-16', '2017-11-30')
    ,  ('B3L9 ',56100.00  ,'2017-12-01', '2020-11-15')
    ,  ('B3L9 ',56664.00  ,'2020-11-16', '2099-12-31')
    ,  ('B6L9 ',64140.00  ,'2013-01-01', '2015-08-15')
    ,  ('B6L9 ',66900.00  ,'2015-08-16', '2017-11-30')
    ,  ('B6L9 ',68244.00  ,'2017-12-01', '2020-11-15')
    ,  ('B6L9 ',68928.00  ,'2020-11-16', '2099-12-31')
    ,  ('M115 ',108528.00 ,'2012-07-01', '2015-06-30')
    ,  ('M115 ',115128.00 ,'2015-07-01', '2099-12-31');