27,579
社区成员
发帖
与我相关
我的任务
分享如何查询人员薪资每次调整的金额
--需求描述：
--通过工资表，查询人员共调了几次薪资，每次调整的金额是多少（列能否动态展开）
--工资表 [人员]、[薪资年月]、[工资]
DECLARE @T1 TABLE (PersonName VARCHAR(100),YearMonth int,Amount decimal(19,9))
INSERT INTO @T1
SELECT '张三','201901','6000' UNION ALL
SELECT '李四','201901','5000' UNION ALL
SELECT '张三','201902','6000' UNION ALL
SELECT '李四','201902','5000' UNION ALL
SELECT '王五','201902','7000' UNION ALL
SELECT '张三','201903','8000' UNION ALL --+2000
SELECT '李四','201903','5000' UNION ALL
SELECT '王五','201903','7500' UNION ALL --+500
SELECT '张三','201904','8500' UNION ALL --+500
SELECT '李四','201904','5000' UNION ALL
SELECT '王五','201904','7500' UNION ALL
--想要的结果
人员 初始薪资 调薪后 调薪1 调薪2 调薪3...
张三 6000 8500 2000 500
李四 5000 5000
王五 7000 7500 500
...全文
请发表友善的回复…
发表回复
小野马1209 2019-12-30
- 打赏
- 举报
[/quote]
--需求描述：
--通过工资表，查询人员共调了几次薪资
--工资表 [人员]、[薪资年月]、[工资]
DECLARE @T1 TABLE (PersonName VARCHAR(100),YearMonth int,Amount decimal(19,9))
INSERT INTO @T1
SELECT '张三','201901','6000' UNION ALL
SELECT '李四','201901','5000' UNION ALL
SELECT '张三','201902','6000' UNION ALL
SELECT '李四','201902','5000' UNION ALL
SELECT '王五','201902','7000' UNION ALL
SELECT '张三','201903','8000' UNION ALL --+2000
SELECT '李四','201903','5000' UNION ALL
SELECT '王五','201903','7500' UNION ALL --+500
SELECT '张三','201904','8500' UNION ALL --+500
SELECT '李四','201904','5000' UNION ALL
SELECT '王五','201904','7500' UNION ALL
SELECT '张三','201906','9500' UNION ALL --+500
SELECT '李四','201906','5000' UNION ALL
SELECT '王五','201906','7500'
SELECT * INTO #TEMP FROM
(SELECT *,CAST('' AS VARCHAR(50)) AS T,
CAST(0 AS DECIMAL(19,9)) AS DIFF ,
PrevAmount=(select top 1 Amount from @T1 A WHERE A.PersonName=B.PersonName AND A.YearMonth<B.YearMonth order by YearMonth DESC),
FirstAmount=(select top 1 Amount from @T1 A WHERE A.PersonName=B.PersonName order by YearMonth),
LastAmount=(select top 1 Amount from @T1 A WHERE A.PersonName=B.PersonName order by YearMonth desc)
FROM @T1 B) A
--删除没有异动数据
DELETE FROM #TEMP WHERE Amount=PrevAmount
--SELECT ROW_NUMBER()OVER(PARTITION BY PersonName order by yearmonth) as No,* from #TEMP
SELECT * INTO #TE2 FROM (SELECT ROW_NUMBER()OVER(PARTITION BY PersonName order by yearmonth) as No,* from #TEMP) A
---处理调薪次数字段
UPDATE #TE2 SET T='[第' + CAST(No-1 as varchar(50)) + '调薪]',DIFF=Amount-PrevAmount
SELECT * FROM #TE2
--处理拼接SQL
DECLARE @NAME VARCHAR(MAX),@SQL VARCHAR(MAX)
SET @NAME=STUFF((SELECT DISTINCT ',' + T FROM #TE2 WHERE T<>'[第0调薪]' FOR XML PATH('')),1,1,'')
SET @sql='WITH CTE AS (SELECT PersonName,T,dIFF,FirstAmount,LastAmount From #TE2)'
SET @sql= @sql + 'SELECT * FROM CTE PIVOT(SUM(Diff) FOR T IN ('
+ @NAME + ')) AS p';
EXEC(@SQL)
小野马1209 2019-12-28
- 打赏
- 举报
[/code][/quote]学习了你的思路终于学能自己写出来了，谢谢！您是开发人员吗？
小野马1209 2019-12-19
- 打赏
- 举报
非常感谢，你的语法读取比较轻松，思路很清晰
Hello World, 2019-12-18
- 打赏
- 举报
去掉OR PrevAmount IS NULL之后
设置Seq的语句要改下，从0开始：
SET @seq = CASE WHEN @name = PersonName THEN @seq + 1 ELSE 0 END
设置Seq的语句要改下，从0开始：
SET @seq = CASE WHEN @name = PersonName THEN @seq + 1 ELSE 0 END
Hello World, 2019-12-18
- 打赏
- 举报
使用临时表和动态SQL查询
DECLARE @name VARCHAR(100) = '',
@seq INT = 0;
--生成临时表
SELECT *,
CAST('' AS NVARCHAR(10)) T,
CAST(0 AS INT) seq,
CAST(0 AS DECIMAL(19,9)) Diff,
LAG(Amount) OVER (PARTITION BY PersonName ORDER BY YearMonth) AS PrevAmount,
FIRST_VALUE(Amount) OVER (PARTITION BY PersonName ORDER BY YearMonth RANGE UNBOUNDED PRECEDING) FirstAmount,
LAST_VALUE(Amount) OVER (PARTITION BY PersonName
ORDER BY YearMonth
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastAmount
INTO #t
FROM @T1
ORDER BY PersonName,
YearMonth;
--删除掉没有调整的数据
DELETE FROM #t WHERE PrevAmount = Amount; --加上 OR PrevAmount IS NULL则不显示无调整记录的人员
--更新调整次数及差异
UPDATE #t
SET @seq = CASE WHEN @name = PersonName THEN @seq + 1 ELSE 1 END,
@name = PersonName,
Diff = Amount-PrevAmount,
T = '调' + CAST(@seq AS VARCHAR(10)),--列名
seq = @seq;
--拼出SQL，动态查询
DECLARE @maxSeq INT = 0; --最大调整次数
DECLARE @columns NVARCHAR(MAX) = ''; --列名
DECLARE @sql NVARCHAR(MAX);
SELECT @maxSeq = MAX(seq)FROM #t;
WHILE @maxSeq > 0
BEGIN
SET @columns = '[调' + CAST(@maxSeq AS VARCHAR(10)) + ']'
+ CASE WHEN @columns <> '' THEN ',' + @columns ELSE '' END;
SET @maxSeq = @maxSeq - 1;
END;
SET @sql = 'SELECT * FROM (SELECT PersonName, T,FirstAmount,LastAmount, Diff FROM #t) a PIVOT(min(Diff) FOR T IN ('
+ @columns + ')) AS p';
--执行动态SQL
EXEC(@sql);
文盲老顾 2019-12-18
- 打赏
- 举报
;with t as (
select *,row_number() over(partition by personName order by yearmonth) as m from @t1
),t1 as (
select * from t where m=1
union all
select * from t a
where m>1 and Amount<>(select Amount from t where m=a.m-1 and PersonName=a.PersonName)
),t2 as (
select *,row_number() over(partition by personName order by m) as o from t1
),t3 as (
select PersonName,Amount as 初始薪资
,(select top 1 Amount from t2 where PersonName=a.PersonName order by o desc) as 调薪后
from t2 a
where o=1
),t4 as (
select PersonName,[2] as 第一次调薪,[3] as 第二次调薪,[4] as 第三次调薪 from (
select PersonName,o,调薪幅度
from t2 a
cross apply(
select a.Amount-Amount as 调薪幅度
from t2
where o=a.o-1 and PersonName=a.PersonName
) b
where o>1
) a
pivot(max(调薪幅度) for o in ([2],[3],[4])) p
)
select a.*,第一次调薪,第二次调薪,第三次调薪
from t3 a
left join t4 b on a.PersonName=b.PersonName
文盲老顾 2019-12-18
- 打赏
- 举报
;with t as (
select *,row_number() over(partition by personname order by yearmonth) as times from @t1
),t1 as (
select *,convert(decimal(19,9),Amount) as n from t where times=1
union all
select a.*,convert(decimal(19,9),a.Amount-b.Amount) as n
from t a,t1 b
where a.PersonName=b.PersonName and a.times=b.times+1
)
select a.PersonName,Amount as 初始薪资,调薪后,第一次调薪,第二次调薪,第三次调薪
from t a
left join (
select PersonName,[2] as 第一次调薪,[3] as 第二次调薪,[4] as 第三次调薪 from (
select PersonName,n,row_number() over(partition by personName order by times) as sn from t1 where n<>0
) a
pivot(
max(n) for sn in ([2],[3],[4])
) p
) b on a.PersonName=b.PersonName
cross apply (
select top 1 Amount as 调薪后 from t where PersonName=a.PersonName order by times desc
) c
where times=1
RINK_1 2019-12-18
- 打赏
- 举报
这段执行有报错，能不能再帮忙看看 消息 207，级别 16，状态 1，第 23 行
列名 'P' 无效。[/quote]
原来报错可能是因为变量@SQL的长度定义短了。这次直接没用你的表变量，把表变量改成临时表了，你再试试下面的，我这里能运行，没报错。
IF OBJECT_ID(N'TEMPDB.DBO.#T1') IS NOT NULL
DROP TABLE #T1
GO
CREATE TABLE #T1(PersonName VARCHAR(100),YearMonth int,Amount decimal(19,9))
INSERT INTO #T1
SELECT '张三','201901','6000' UNION ALL
SELECT '李四','201901','5000' UNION ALL
SELECT '张三','201902','6000' UNION ALL
SELECT '李四','201902','5000' UNION ALL
SELECT '王五','201902','7000' UNION ALL
SELECT '张三','201903','5000' UNION ALL --+2000
SELECT '李四','201903','5000' UNION ALL
SELECT '王五','201903','7500' UNION ALL --+500
SELECT '张三','201904','8500' UNION ALL --+500
SELECT '李四','201904','5000' UNION ALL
SELECT '王五','201904','7500'
GO
DECLARE @SQL VARCHAR(MAX);
WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY PERSONNAME ORDER BY YEARMONTH) AS SEQ_1,
DENSE_RANK() OVER (PARTITION BY PERSONNAME ORDER BY AMOUNT) AS SEQ_2
FROM #T1),
CTE_1
AS
(SELECT *,NEXT_AMOUNT-PRE_AMOUNT AS DIFF,
ROW_NUMBER() OVER (PARTITION BY PERSONNAME ORDER BY SEQ_1) AS SEQ_3,
COUNT(1) OVER (PARTITION BY PERSONNAME) AS SEQ_4
FROM
(SELECT A.PersonName,A.SEQ_1,A.AMOUNT AS PRE_AMOUNT,B.AMOUNT AS NEXT_AMOUNT
FROM CTE A
JOIN CTE B ON A.PersonName=B.PersonName AND A.SEQ_1=B.SEQ_1-1
WHERE A.Amount<>B.Amount) AS A)
SELECT @SQL=ISNULL(@SQL+',','')+'SUM(CASE WHEN SEQ_3='+CAST(SEQ_3 AS VARCHAR)+' AND DIFF<>0 THEN DIFF ELSE 0 END) AS DIFF'+CAST(SEQ_3 AS VARCHAR)
FROM
(SELECT DISTINCT SEQ_3 FROM CTE_1) AS A
SET @SQL='WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY PERSONNAME ORDER BY YEARMONTH) AS SEQ_1,
DENSE_RANK() OVER (PARTITION BY PERSONNAME ORDER BY AMOUNT) AS SEQ_2
FROM #T1),
CTE_1
AS
(SELECT *,NEXT_AMOUNT-PRE_AMOUNT AS DIFF,
ROW_NUMBER() OVER (PARTITION BY PERSONNAME ORDER BY SEQ_1) AS SEQ_3,
COUNT(1) OVER (PARTITION BY PERSONNAME) AS SEQ_4
FROM
(SELECT A.PersonName,A.SEQ_1,A.AMOUNT AS PRE_AMOUNT,B.AMOUNT AS NEXT_AMOUNT
FROM CTE A
JOIN CTE B ON A.PersonName=B.PersonName AND A.SEQ_1=B.SEQ_1-1
WHERE A.Amount<>B.Amount) AS A),
CTE_2
AS
(SELECT * FROM CTE_1
UNION ALL
SELECT PERSONNAME,1,AMOUNT,AMOUNT,0,1,1
FROM CTE A
WHERE NOT EXISTS (SELECT 1 FROM CTE WHERE SEQ_2>1 AND A.PERSONNAME=PERSONNAME)
AND SEQ_1=1)
SELECT PERSONNAME,SUM(CASE WHEN SEQ_3=1 THEN PRE_AMOUNT ELSE 0 END) AS INI_AMOUNT,
SUM(CASE WHEN SEQ_3=SEQ_4 THEN NEXT_AMOUNT ELSE 0 END) AS CUR_AMOUNT,'
+@SQL+'
FROM CTE_2 GROUP BY PERSONNAME'
EXEC(@SQL)
Hello World, 2019-12-18
- 打赏
- 举报
不知你的是什么版本，我在2008和2014下面试过都正常。如果有这个风险可以改成用row_number()来排名
DECLARE @name VARCHAR(100) = '',
@seq INT = 0;
--生成临时表
SELECT *,
CAST('' AS NVARCHAR(10)) T,
CAST(0 AS INT) seq,
CAST(0 AS DECIMAL(19,9)) Diff,
(SELECT TOP 1 Amount FROM @T1 a WHERE a.PersonName=t.PersonName AND a.YearMonth<t.YearMonth ORDER BY a.YearMonth DESC) PrevAmount,
(SELECT TOP 1 a.Amount FROM @T1 a WHERE a.PersonName=t.PersonName ORDER BY a.YearMonth) FirstAmount,
(SELECT TOP 1 a.Amount FROM @T1 a WHERE a.PersonName=t.PersonName ORDER BY a.YearMonth DESC) LastAmount
INTO #t
FROM @T1 t
ORDER BY PersonName,
YearMonth;
--删除掉没有调整的数据
DELETE FROM #t WHERE PrevAmount = Amount; --加上 OR PrevAmount IS NULL则不显示无调整记录的人员，不显示时seq不减1
SELECT *,ROW_NUMBER() OVER (PARTITION BY PersonName ORDER BY YearMonth) RowNumber INTO #t2 FROM #t
--更新调整次数及差异
UPDATE #t2
SET Diff = Amount - PrevAmount,
T = '调' + CAST(RowNumber - 1 AS VARCHAR(10)), --列名
seq = RowNumber - 1;
--拼出SQL，动态查询
DECLARE @maxSeq INT = 0; --最大调整次数
DECLARE @columns NVARCHAR(MAX) = ''; --列名
DECLARE @sql NVARCHAR(MAX);
SELECT @maxSeq = MAX(seq)FROM #t2;
WHILE @maxSeq > 0
BEGIN
SET @columns = '[调' + CAST(@maxSeq AS VARCHAR(10)) + ']'
+ CASE WHEN @columns <> '' THEN ',' + @columns ELSE '' END;
SET @maxSeq = @maxSeq - 1;
END;
SET @sql = 'SELECT * FROM (SELECT PersonName, T,FirstAmount,LastAmount, Diff FROM #t2) a PIVOT(min(Diff) FOR T IN ('
+ @columns + ')) AS p';
--执行动态SQL
EXEC(@sql);
DROP TABLE #t;
DROP TABLE #t2;
文盲老顾 2019-12-18
- 打赏
- 举报
哦，我刚试了下这个指令，发现#t表的seq值有问题，然后发现在select into #t order 时，插入顺序没按照order走
所以 seq 异常后，结果和你的不一致了
所以 seq 异常后，结果和你的不一致了
Hello World, 2019-12-18
- 打赏
- 举报
如果出现降薪，然后又加薪，且幅度一致，你的这个语句会缺失调薪信息[/quote]
没有缺失啊，都有记录的：
文盲老顾 2019-12-18
- 打赏
- 举报
如果出现降薪，然后又加薪，且幅度一致，你的这个语句会缺失调薪信息
文盲老顾 2019-12-18
- 打赏
- 举报
DECLARE @T1 TABLE (PersonName VARCHAR(100),YearMonth int,Amount decimal(19,9))
INSERT INTO @T1
SELECT '张三','201901','6000' UNION ALL
SELECT '李四','201901','5000' UNION ALL
SELECT '张三','201902','6000' UNION ALL
SELECT '李四','201902','5000' UNION ALL
SELECT '王五','201902','7000' UNION ALL
SELECT '张三','201903','8000' UNION ALL --+2000
SELECT '李四','201903','5000' UNION ALL
SELECT '王五','201903','7500' UNION ALL --+500
SELECT '张三','201904','8500' UNION ALL --+500
SELECT '张三','201905','8800' UNION ALL --+500
SELECT '张三','201906','9200' UNION ALL --+500
SELECT '张三','201907','9600' UNION ALL --+500
SELECT '张三','201908','10500' UNION ALL --+500
SELECT '张三','201909','11000' UNION ALL --+500
SELECT '张三','201910','12000' UNION ALL --+500
SELECT '张三','201911','10500' UNION ALL --+500
SELECT '张三','201912','12000' UNION ALL --+500
SELECT '李四','201904','5000' UNION ALL
SELECT '王五','201904','7500'
select *,row_number() over(partition by personName order by yearmonth) as m
into #tx
from @t1
select * into #t1 from (
select * from #tx where m=1
union all
select * from #tx a
where m>1 and Amount<>(select Amount from #tx where m=a.m-1 and PersonName=a.PersonName)
) a
select *,row_number() over(partition by personName order by m) as o into #t2 from #t1
select PersonName,Amount as 初始薪资
,(select top 1 Amount from #t2 where PersonName=a.PersonName order by o desc) as 调薪后
into #t3
from #t2 a
where o=1
declare @sql nvarchar(max)
set @sql = ' select PersonName' + (select distinct ',['+convert(varchar,o)+'] as 调薪' + convert(varchar,o-1) from #t2 where o>1 for xml path('')) + '
into #t4
from (
select PersonName,o,调薪幅度
from #t2 a
cross apply(
select a.Amount-Amount as 调薪幅度
from #t2
where o=a.o-1 and PersonName=a.PersonName
) b
where o>1
) a
pivot(max(调薪幅度) for o in (' + stuff((select distinct ',['+convert(varchar,o)+']' from #t2 where o>1 for xml path('')),1,1,'') + ')) p
select a.*' + (select ',调薪' + convert(varchar,o-1) from #t2 where o>1 group by o order by o for xml path('')) + '
from #t3 a
left join #t4 b on a.PersonName=b.PersonName
'
exec(@sql)
drop table #tx
drop table #t1
drop table #t2
drop table #t3
Hello World, 2019-12-18
- 打赏
- 举报
换掉几个新版本才有的分析函数：
DECLARE @name VARCHAR(100) = '',
@seq INT = 0;
--生成临时表
SELECT *,
CAST('' AS NVARCHAR(10)) T,
CAST(0 AS INT) seq,
CAST(0 AS DECIMAL(19,9)) Diff,
(SELECT TOP 1 Amount FROM @T1 a WHERE a.PersonName=t.PersonName AND a.YearMonth<t.YearMonth ORDER BY a.YearMonth DESC) PrevAmount,
(SELECT TOP 1 a.Amount FROM @T1 a WHERE a.PersonName=t.PersonName ORDER BY a.YearMonth) FirstAmount,
(SELECT TOP 1 a.Amount FROM @T1 a WHERE a.PersonName=t.PersonName ORDER BY a.YearMonth DESC) LastAmount
INTO #t
FROM @T1 t
ORDER BY PersonName,
YearMonth;
--删除掉没有调整的数据
DELETE FROM #t WHERE PrevAmount = Amount; --加上 OR PrevAmount IS NULL则不显示无调整记录的人员
--更新调整次数及差异
UPDATE #t
SET @seq = CASE WHEN @name = PersonName THEN @seq + 1 ELSE 0 END,
@name = PersonName,
Diff = Amount-PrevAmount,
T = '调' + CAST(@seq AS VARCHAR(10)),--列名
seq = @seq;
--拼出SQL，动态查询
DECLARE @maxSeq INT = 0; --最大调整次数
DECLARE @columns NVARCHAR(MAX) = ''; --列名
DECLARE @sql NVARCHAR(MAX);
SELECT @maxSeq = MAX(seq)FROM #t;
WHILE @maxSeq > 0
BEGIN
SET @columns = '[调' + CAST(@maxSeq AS VARCHAR(10)) + ']'
+ CASE WHEN @columns <> '' THEN ',' + @columns ELSE '' END;
SET @maxSeq = @maxSeq - 1;
END;
SET @sql = 'SELECT * FROM (SELECT PersonName, T,FirstAmount,LastAmount, Diff FROM #t) a PIVOT(min(Diff) FOR T IN ('
+ @columns + ')) AS p';
--执行动态SQL
EXEC(@sql);
文盲老顾 2019-12-18
- 打赏
- 举报
select *,row_number() over(partition by personName order by yearmonth) as m
into #t
from @t1
select * into #t1 from (
select * from #t where m=1
union all
select * from #t a
where m>1 and Amount<>(select Amount from #t where m=a.m-1 and PersonName=a.PersonName)
) a
select *,row_number() over(partition by personName order by m) as o into #t2 from #t1
select PersonName,Amount as 初始薪资
,(select top 1 Amount from #t2 where PersonName=a.PersonName order by o desc) as 调薪后
into #t3
from #t2 a
where o=1
declare @sql nvarchar(max)
set @sql = ' select PersonName' + (select distinct ',['+convert(varchar,o)+'] as 调薪' + convert(varchar,o-1) from #t2 where o>1 for xml path('')) + '
into #t4
from (
select PersonName,o,调薪幅度
from #t2 a
cross apply(
select a.Amount-Amount as 调薪幅度
from #t2
where o=a.o-1 and PersonName=a.PersonName
) b
where o>1
) a
pivot(max(调薪幅度) for o in (' + stuff((select distinct ',['+convert(varchar,o)+']' from #t2 where o>1 for xml path('')),1,1,'') + ')) p
select a.*' + (select distinct ',调薪' + convert(varchar,o-1) from #t2 where o>1 for xml path('')) + '
from #t3 a
left join #t4 b on a.PersonName=b.PersonName
'
exec(@sql)
drop table #t
drop table #t1
drop table #t2
drop table #t3小野马1209 2019-12-18
- 打赏
- 举报
第几次能不写固定的吗，例如一个员工工作15年，可能会有10次以上调薪的记录
小野马1209 2019-12-18
- 打赏
- 举报
这段执行有报错，能不能再帮忙看看 消息 207，级别 16，状态 1，第 23 行
列名 'P' 无效。
列名 'P' 无效。
小野马1209 2019-12-18
- 打赏
- 举报
执行报错是因为这个版本还没有LAG函数吗：'LAG' 不是可以识别的 内置函数名称。 我的SQL版本是2008R2（SP2）
RINK_1 2019-12-18
- 打赏
- 举报
SELECT *,ROW_NUMBER() OVER (PARTITION BY PERSONNAME ORDER BY YEARMONTH) AS SEQ_1,
DENSE_RANK() OVER (PARTITION BY PERSONNAME ORDER BY AMOUNT) AS SEQ_2
INTO #A
FROM @T1
DECLARE @SQL VARCHAR(1000)
;WITH CTE_1
AS
(SELECT *,NEXT_AMOUNT-PRE_AMOUNT AS DIFF,
ROW_NUMBER() OVER (PARTITION BY PERSONNAME ORDER BY SEQ_1) AS SEQ_3,
COUNT(1) OVER (PARTITION BY PERSONNAME) AS SEQ_4
FROM
(SELECT A.PersonName,A.SEQ_1,A.AMOUNT AS PRE_AMOUNT,B.AMOUNT AS NEXT_AMOUNT
FROM #A A
JOIN #A B ON A.PersonName=B.PersonName AND A.SEQ_1=B.SEQ_1-1
WHERE A.Amount<>B.Amount) AS A)
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN SEQ_3='+CAST(SEQ_3 AS VARCHAR)+' AND DIFF<>0 THEN DIFF ELSE 0 END) AS DIFF'+CAST(SEQ_3 AS VARCHAR)
FROM
(SELECT DISTINCT SEQ_3 FROM CTE_1) AS A
SET @SQL='WITH CTE_1
AS
(SELECT *,NEXT_AMOUNT-PRE_AMOUNT AS DIFF,
ROW_NUMBER() OVER (PARTITION BY PERSONNAME ORDER BY SEQ_1) AS SEQ_3,
COUNT(1) OVER (PARTITION BY PERSONNAME) AS SEQ_4
FROM
(SELECT A.PersonName,A.SEQ_1,A.AMOUNT AS PRE_AMOUNT,B.AMOUNT AS NEXT_AMOUNT
FROM #A A
JOIN #A B ON A.PersonName=B.PersonName AND A.SEQ_1=B.SEQ_1-1
WHERE A.Amount<>B.Amount) AS A),
CTE_2
AS
(SELECT * FROM CTE_1
UNION ALL
SELECT PERSONNAME,1,AMOUNT,AMOUNT,0,1,1
FROM #A A
WHERE NOT EXISTS (SELECT 1 FROM #A WHERE SEQ_2>1 AND A.PERSONNAME=PERSONNAME)
AND SEQ_1=1)
SELECT PERSONNAME,SUM(CASE WHEN SEQ_3=1 THEN PRE_AMOUNT ELSE 0 END) AS INI_AMOUNT,
SUM(CASE WHEN SEQ_3=SEQ_4 THEN NEXT_AMOUNT ELSE 0 END) AS CUR_AMOUNT,'
+@SQL+'
FROM CTE_2 GROUP BY PERSONNAME'
EXEC(@SQL)
