clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591691 fiddles created (45710 in the last week).

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'); SELECT * FROM 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 本薪 2021-09-01 晉升 24800
12345 津貼2 2021-09-01 晉升 510
12345 全勤獎 2021-10-01 其他 110
12345 津貼1 2021-10-01 其他 490
24680 津貼2 2021-04-01 2021-08-31 其他 4109
24680 本薪 2021-04-01 2021-08-31 其他 238009
24680 全勤獎 2021-04-01 2021-09-30 其他 1009
24680 津貼1 2021-04-01 2021-09-30 其他 220
24680 津貼2 2021-05-01 晉升 5107
24680 本薪 2021-07-01 晉升 218009
24680 津貼2 2021-08-01 晉升 5108
24680 本薪 2021-10-01 晉升 228009
24680 津貼2 2021-11-01 晉升 5109
24680 全勤獎 2021-12-01 其他 1109
24680 津貼1 2021-12-01 其他 4909
 hidden batch(es)


-- 版主原 SQL 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
empno effecttime 本薪 全勤獎 津貼1 津貼2
12345 2021-03-01 23800 100 0 410
12345 2021-09-01 24800 510
12345 2021-10-01 110 490
24680 2021-04-01 238009 1009 220 4109
24680 2021-05-01 5107
24680 2021-07-01 218009
24680 2021-08-01 5108
24680 2021-10-01 228009
24680 2021-11-01 5109
24680 2021-12-01 1109 4909
 hidden batch(es)


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
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
24680 2021-04-01 238009 1009 220 4109
24680 2021-05-01 238009 1009 220 5107
24680 2021-07-01 218009 1009 220 5107
24680 2021-08-01 218009 1009 220 5108
24680 2021-10-01 228009 1009 220 5108
24680 2021-11-01 228009 1009 220 5109
24680 2021-12-01 228009 1109 4909 5109
 hidden batch(es)