返回填充空值的每个月的最新值

Question

在SQL Server 2017中，我有一个看起来像这样的表https://i.stack.imgur.com/Ry106.png，我想在每个月底获得成员的数量，用上个月的数据填写空白月份。

所以有这个表

Create table #tempCenters (
OperationId int identity (1,1) primary key,
CenterId int,
members int,
Change_date date,
Address varchar(100), --non relevant
Sales float --non relevant
)

使用此数据

INSERT INTO #tempCenters VALUES 
(1, 100, '2020-02-20', 'non relevant column', 135135),
(1, 110, '2020-04-15', 'non relevant column', 231635),
(1, 130, '2020-04-25', 'non relevant column', 3565432),
(1, 180, '2020-09-01', 'non relevant column', 231651),
(2, 200, '2020-01-20', 'non relevant column', 321365),
(2, 106, '2020-03-20', 'non relevant column', 34534),
(2, 135, '2020-06-25', 'non relevant column', 3224),
(2, 154, '2020-06-20', 'non relevant column', 2453453)

我期待这个结果

CenterId, Members, EOM_Date 
1, 100, '2020-2-28'
1, 100, '2020-3-30'
1, 130, '2020-4-31'
1, 130, '2020-5-30'
1, 130, '2020-6-31'
1, 130, '2020-7-31'
1, 130, '2020-8-30'
1, 180, '2020-9-31'
2, 200, '2020-1-31'
2, 200, '2020-2-28'
2, 106, '2020-3-31'
2, 106, '2020-4-30'
2, 106, '2020-5-31'
2, 135, '2020-6-30'

这是我目前得到的

SELECT 
t.centerId, 
EOMONTH(t.Change_date) as endOfMonthDate, 
t.members
FROM #tempCenters t
RIGHT JOIN (
SELECT 
S.CenterId, 
Year(S.Change_date) as dateYear, 
Month(S.Change_date) as dateMonth, 
Max(s.OperationId) as id
FROM   #tempCenters S
GROUP BY CenterId, Year(Change_date), Month(Change_date)
) A
ON A.id = t.OperationId

返回每个月的值，但不填空。

Answer 1

首先我得到每个CenterId的开始日期(最小日期)和完成日期(最大日期)。然后为每个CenterId生成从开始日期到结束日期的所有月末。最后，我将子查询(cte)与您的表(在cte.CenterId = tc.CenterId AND cte.EOM_Date >= tc.Change_date上)连接起来，并获得每个日期(月末)的最后(以前或相同日期)成员值。

WITH cte AS (SELECT CenterId, EOMONTH(MIN(Change_date)) AS EOM_Date, EOMONTH(MAX(Change_date)) AS finish
FROM #tempCenters
GROUP BY CenterId

UNION ALL 

SELECT CenterId, EOMONTH(DATEADD(MONTH, 1, EOM_Date)), finish
FROM cte
WHERE EOM_Date < finish)


SELECT DISTINCT cte.CenterId, 
FIRST_VALUE(Members) OVER(PARTITION BY cte.CenterId, cte.EOM_Date ORDER BY tc.Change_date DESC) AS Members, 
cte.EOM_Date 
FROM cte 
LEFT JOIN #tempCenters tc ON cte.CenterId = tc.CenterId AND cte.EOM_Date >= tc.Change_date
ORDER BY CenterId, EOM_Date;

Answer 2

我知道它看起来很麻烦，我确信有一个更优雅的解决方案，但是仍然可以使用子查询的联合所有和外部应用的组合来获得所需的结果。

Select t.CenterId, Coalesce(t.members, tt.members), t.Change_date
From (
Select CenterId, Max(members) As members, Change_date
From
(Select t.CenterId, t.members, EOMONTH(t.Change_date) As Change_date
From #tempCenters As t Inner Join 
(Select CenterId, Max(Change_date) As Change_date
From #tempCenters 
Group by CenterId, Year(Change_date), Month(Change_date)
) As tt On (t.CenterId=tt.CenterId And
t.Change_date=tt.Change_date)
Union All
Select t.CenterId, Null As member, t.Change_date
From (
Select tt.CenterId, EOMONTH(datefromparts(tt.[YEAR], t.[MONTH], '1')) As Change_date,
Min_Change_date, Max_Change_date
From (Select [value] as [Month] From OPENJSON('[1,2,3,4,5,6,7,8,9,10,11,12]')) As t, 
(Select CenterId, Year(Change_date) As [YEAR], 
Min(Change_date) As Min_Change_date, Max(Change_date) As Max_Change_date
From #tempCenters Group by CenterId, Year(Change_date)) As tt) As t
Where Change_date Between Min_Change_date And Max_Change_date) As t
Group by CenterId, Change_date) As t Outer Apply 
(Select members 
From #tempCenters 
Where CenterId=t.CenterId And
Change_date = (Select Max(Change_date) 
From #tempCenters Where CenterId=t.CenterId And Change_date<t.Change_date Group by CenterId)) As tt
Order by t.CenterId, t.Change_date