我有一個表如下：
EmpID FromYM ToYM EmpYl EmpX1
----- ------ ----- ------ ----------
1001 202101 202101 20210103 20210103
1001 202102 202103 20210103 20210103
我想根據上述資料拆分這些資料。
如果FromYm(Means yearmonth) 和ToYM(Means yearmonth) 差為 2，則結果為兩行：
示例結果：
EmpID FromYM ToYM EmpYl DiffNoCount
------ ------ ---- ----- ------
1001 202101 202101 20210103 1
1001 202102 202103 20210103 1
1001 202102 202103 20210103 2
試過的代碼
IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
BEGIN
DROP TABLE #rawdata1
END
IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
BEGIN
DROP TABLE #rawdata2
END
go
declare @Max as int
declare @Kount as int
Set @Kount = 1
SELECT row_number() Over (order by EmpID) as row,EmpID
,FromYM
,ToYM
, EmpYl
,EmpX1
into #rawdata1
FROM [dbo].[ASAAValue1]
order by EmpID
set @Max = (Select Max(FromYM) from #rawdata1)
Create Table #Rawdata2
(
[Row] int,
Rolling int,
RollingAvg decimal(15,2),
RollingFinal int
)
while (@kount < @max)
Begin
Insert into #rawdata2
select @Kount as Row , FromYM as Rolling
, ToYM as RollingAvg,
Case When Convert(int,Convert(nvarchar(6),EmpYl))>=FromYM
Then <br>
FromYM <br>
Else<br>
FromYM 1 <br>
End <br>
from #rawdata1<br>
where row between @Kount - 12 and @Kount <br>
set @Kount = @Kount 1
end
select rd1.row,
rd1.EmpID,Rd1.FromYM,Rd1.ToYM,Rd1.EmpYl, rd2.RollingFinal as Final from #rawdata2 rd2
inner join #rawdata1 rd1
on rd1.row = rd2.row
uj5u.com熱心網友回復：
您可以使用遞回 cte 來做到這一點：
with data as (
select '202101' as f, '202101' as t union all
select '202102' as f, '202103' as t
),
rcte as (
select *, 1 as n from data
union all
select f, t, n 1
from rcte
where n <= datediff(month, convert(date, f '01', 112), convert(date, t '01', 112))
)
select * from rcte
你可以在這個db<>fiddle上測驗
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/371556.html
標籤：sql sql-server 查询语句