CREATE TABLE [HisPay](
[empno] NVARCHAR(20) NULL,
[subjectname] NVARCHAR(20) NULL,
[effecttime] NVARCHAR(20) NULL,
[disabledtime] NVARCHAR(20) NULL,
[reason] NVARCHAR(20) NULL,
[value] NVARCHAR(20) NULL
);

INSERT INTO [HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) 
VALUES ('12345',N'津貼2','2021-03-01','2021-08-31',N'其他','410'),
('12345',N'本薪','2021-03-01','2021-08-31',N'其他','23800'),
('12345',N'全勤獎','2021-03-01','2021-09-30',N'其他','100'),
('12345',N'津貼1','2021-03-01','2021-09-30',N'其他','0'),
('12345',N'本薪','2021-09-01','',N'晉升','24800'),
('12345',N'津貼2','2021-09-01','',N'晉升','510'),
('12345',N'全勤獎','2021-10-01','',N'其他','110'),
('12345',N'津貼1','2021-10-01','',N'其他','490'),
--
('24680',N'津貼2','2021-04-01','2021-08-31',N'其他','4109'),
('24680',N'本薪','2021-04-01','2021-08-31',N'其他','238009'),
('24680',N'全勤獎','2021-04-01','2021-09-30',N'其他','1009'),
('24680',N'津貼1','2021-04-01','2021-09-30',N'其他','220'),
('24680',N'津貼2','2021-05-01','',N'晉升','5107'),
('24680',N'本薪','2021-07-01','',N'晉升','218009'),
('24680',N'津貼2','2021-08-01','',N'晉升','5108'),
('24680',N'本薪','2021-10-01','',N'晉升','228009'),
('24680',N'津貼2','2021-11-01','',N'晉升','5109'),
('24680',N'全勤獎','2021-12-01','',N'其他','1109'),
('24680',N'津貼1','2021-12-01','',N'其他','4909');

WITH CTE_X1 AS (
SELECT empno, effecttime, 
ISNULL([本薪],NULL) 本薪, 
ISNULL([全勤獎],NULL) 全勤獎, 
ISNULL([津貼1],NULL) 津貼1 , 
ISNULL([津貼2],NULL) 津貼2
FROM (
select empno, subjectname, effecttime, reason, value from HisPay
) as sourecTable
PIVOT( MAX( sourecTable.value )
FOR sourecTable.subjectname in ([本薪], [全勤獎], [津貼1], [津貼2] )
) PB),
CTE_X2 AS (
SELECT empno,effecttime AS effecttimeSTART,本薪,
LEAD(effecttime, 1, FORMAT(DATEADD(D, 30000, effecttime),'yyyy-MM-dd'))
OVER (PARTITION BY empno ORDER BY effecttime) AS effecttimeEND
FROM CTE_X1
WHERE [本薪] IS NOT NULL),
CTE_X3 AS (
SELECT empno,effecttime AS effecttimeSTART,全勤獎,
LEAD(effecttime, 1, FORMAT(DATEADD(D, 30000, effecttime),'yyyy-MM-dd'))
OVER (PARTITION BY empno ORDER BY effecttime) AS effecttimeEND
FROM CTE_X1
WHERE [全勤獎] IS NOT NULL),
CTE_X4 AS (
SELECT empno,effecttime AS effecttimeSTART,津貼1,
LEAD(effecttime, 1, FORMAT(DATEADD(D, 30000, effecttime),'yyyy-MM-dd'))
OVER (PARTITION BY empno ORDER BY effecttime) AS effecttimeEND
FROM CTE_X1
WHERE [津貼1] IS NOT NULL),
CTE_X5 AS (
SELECT empno,effecttime AS effecttimeSTART,津貼2,
LEAD(effecttime, 1, FORMAT(DATEADD(D, 30000, effecttime),'yyyy-MM-dd'))
OVER (PARTITION BY empno ORDER BY effecttime) AS effecttimeEND
FROM CTE_X1
WHERE [津貼2] IS NOT NULL)

SELECT A.empno,A.effecttime,
ISNULL(A.本薪,B.本薪) AS 本薪,
ISNULL(A.全勤獎,C.全勤獎) AS 全勤獎,
ISNULL(A.津貼1,D.津貼1) AS 津貼1,
ISNULL(A.津貼2,E.津貼2) AS 津貼2
FROM CTE_X1 AS A
LEFT JOIN CTE_X2 AS B ON A.empno=B.empno AND A.effecttime>=B.effecttimeSTART
AND A.effecttime<B.effecttimeEND
LEFT JOIN CTE_X3 AS C ON A.empno=C.empno AND A.effecttime>=C.effecttimeSTART
AND A.effecttime<C.effecttimeEND
LEFT JOIN CTE_X4 AS D ON A.empno=D.empno AND A.effecttime>=D.effecttimeSTART
AND A.effecttime<D.effecttimeEND
LEFT JOIN CTE_X5 AS E ON A.empno=E.empno AND A.effecttime>=E.effecttimeSTART
AND A.effecttime<E.effecttimeEND

Demo

寫入原始資料表HISPAY的SQL

CREATE TABLE [HISPAY](
[EMPNO] nvarchar(5),
[SUBJECTNAME] nvarchar(5),
[EFFECTTIME] nvarchar(10),
[DISABLEDTIME] nvarchar(10),
[REASON] nvarchar(5),
[VALUE] nvarchar(5)
);

INSERT INTO [HISPAY] VALUES
('12345',N'津貼2','2021-03-01','2021-08-31',N'其他','410'),
('12345',N'本薪','2021-03-01','2021-08-31',N'其他','23800'),
('12345',N'全勤獎','2021-03-01','2021-09-30',N'其他','100'),
('12345',N'津貼1','2021-03-01','2021-09-30',N'其他','0'),
('12345',N'本薪','2021-09-01','',N'晉升','24800'),
('12345',N'津貼2','2021-09-01','',N'晉升','510'),
('12345',N'全勤獎','2021-10-01','',N'其他','110'),
('12345',N'津貼1','2021-10-01','',N'其他','490')
;
SELECT * FROM [HISPAY];

原始資料表HISPAY

WITH M AS (
 SELECT *
  FROM (SELECT DISTINCT [EMPNO], [EFFECTTIME] FROM [HISPAY]) A
  FULL OUTER JOIN (
    SELECT * 
    FROM (VALUES(N'本薪'),(N'全勤獎'),(N'津貼1'),(N'津貼2')) B(SUBJECTNAME)
    ) B
    ON 1 = 1
),
N AS (
 SELECT [M].*,[B].[VALUE]
  FROM [M]
  LEFT OUTER JOIN [HISPAY] B
    ON (([M].[EFFECTTIME] BETWEEN [B].[EFFECTTIME] AND [B].[DISABLEDTIME]) OR
       (([M].[EFFECTTIME] >= [B].[EFFECTTIME] AND [B].[DISABLEDTIME] = '')))
    AND [M].[SUBJECTNAME] = [B].[SUBJECTNAME]
)

SELECT 
   [EMPNO]
 , [EFFECTTIME] 
 , [本薪] 
 , [全勤獎] 
 , [津貼1] 
 , [津貼2]
FROM (
 SELECT * FROM [N]
) [N]
PIVOT( MAX([N].[VALUE])
FOR [N].[SUBJECTNAME] IN ( [本薪], [全勤獎], [津貼1], [津貼2] )
) PB
;

表格M