iT邦幫忙

0

[SQL]歷史異動資料有生失效區間, 如何用善用PIVOT橫長

請教各位大大, 有一MSSQL調薪歷程資料table HisPay如下:
https://ithelp.ithome.com.tw/upload/images/20210922/20142336XLx3MFHQHj.jpg
語法:
CREATE TABLE [dbo].[HisPay](
[empno] nchar NULL,
[subjectname] nchar NULL,
[effecttime] nchar NULL,
[disabledtime] nchar NULL,
[reason] nchar NULL,
[value] nchar NULL
) ON [PRIMARY]

INSERT INTO [dbo].[HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) VALUES ('12345','津貼2','2021-03-01','2021-08-31','其他','410');
INSERT INTO [dbo].[HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) VALUES ('12345','本薪','2021-03-01','2021-08-31','其他','23800');
INSERT INTO [dbo].[HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) VALUES ('12345','全勤獎','2021-03-01','2021-09-30','其他','100');
INSERT INTO [dbo].[HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) VALUES ('12345','津貼1','2021-03-01','2021-09-30','其他','0');
INSERT INTO [dbo].[HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) VALUES ('12345','本薪','2021-09-01','','晉升','24800');
INSERT INTO [dbo].[HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) VALUES ('12345','津貼2','2021-09-01','','晉升','510');
INSERT INTO [dbo].[HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) VALUES ('12345','全勤獎','2021-10-01','','其他','110');
INSERT INTO [dbo].[HisPay] ([empno] ,[subjectname] ,[effecttime] ,[disabledtime] ,[reason] ,[value]) VALUES ('12345','津貼1','2021-10-01','','其他','490');

每次在調薪生效日(effecttime)只會針對有異動的科目名稱(subjectname)去產生新的資料, 並將異動前的資料押上失效日(disabledtime), 無異動科目名稱失效日仍為空值, 我用PIVOT撈出的結果如下, 只能呈現有異動的紀錄清單:
https://ithelp.ithome.com.tw/upload/images/20210922/20142336gQDsqMc5vI.jpg
語法:
SELECT empno, effecttime, ISNULL([本薪],0) 本薪, ISNULL([全勤獎],0) 全勤獎, ISNULL([津貼1],0) 津貼1 , ISNULL([津貼2],0) 津貼2
FROM (
select empno, subjectname, effecttime, reason, value from HisPay
) as sourecTable
PIVOT( MAX( sourecTable.value )
FOR sourecTable.subjectname in ([本薪], [全勤獎], [津貼1], [津貼2] )
) PB

但我想呈現的是每次異動後的所有科目名稱(subjectname)的值(value)都秀出來, 包含薪資科目沒有異動的部分, 參考如下:
https://ithelp.ithome.com.tw/upload/images/20210922/20142336oPdkzX96if.jpg
請教各位大大要如何撰寫呢? 謝謝!
文長也謝謝大大們願意撥空看完!

2 個回答

2
rogeryao
iT邦大師 1 級 ‧ 2021-09-22 15:12:02
最佳解答
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

0
may031556
iT邦新手 5 級 ‧ 2021-09-26 02:01:49

寫入原始資料表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

EMPNO SUBJECTNAME EFFECTTIME DISABLEDTIME REASON VALUE
12345 津貼2 2021-03-01 2021-08-31 其他 410
12345 本薪 2021-03-01 2021-08-31 其他 23800
12345 全勤獎 2021-03-01 2021-09-30 其他 100
12345 津貼1 2021-03-01 2021-09-30 其他 0
12345 本薪 021-09-01 晉升 24800
12345 津貼2 2021-09-01 晉升 510
12345 全勤獎 2021-10-01 其他 110
12345 津貼1 2021-10-01 其他 490
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

EMPNO EFFECTTIME SUBJECTNAME
12345 2021-03-01 本薪
12345 2021-03-01 全勤獎
12345 2021-03-01 津貼1
12345 2021-03-01 津貼2
12345 2021-09-01 本薪
12345 2021-09-01 全勤獎
12345 2021-09-01 津貼1
12345 2021-09-01 津貼2
12345 2021-10-01 本薪
12345 2021-10-01 全勤獎
12345 2021-10-01 津貼1
12345 2021-10-01 津貼2

表格N

EMPNO EFFECTTIME SUBJECTNAME VALUE
12345 2021-03-01 本薪 23800
12345 2021-03-01 全勤獎 100
12345 2021-03-01 津貼1 0
12345 2021-03-01 津貼2 410
12345 2021-09-01 本薪 24800
12345 2021-09-01 全勤獎 100
12345 2021-09-01 津貼1 0
12345 2021-09-01 津貼2 510
12345 2021-10-01 本薪 24800
12345 2021-10-01 全勤獎 110
12345 2021-10-01 津貼1 490
12345 2021-10-01 津貼2 510

結果

EMPNO EFFECTTIME 本薪 全勤獎 津貼1 津貼2
12345 2021-03-01 23800 100 0 410
12345 2021-09-01 24800 100 0 510
12345 2021-10-01 24800 110 490 510

我要發表回答

立即登入回答