回傳每個月的最新值填充空值

在 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

它回傳每月的值，但不填充空白值。

uj5u.com熱心網友回復：

首先，我獲取每個 CenterId 的開始日期（最小日期）和完成日期（最大日期）。然后我為每個 CenterId 生成從開始日期到結束日期的所有月末。最后，我將我的 subuqery (cte) 與您的表 (on 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;

uj5u.com熱心網友回復：

我知道它看起來很麻煩，而且我確信有一個更優雅的解決方案，但是您仍然可以使用子查詢與 union all 和 external apply 的組合來獲得所需的結果。

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

標籤：sql sql-server 查询语句

上一篇：嘗試創建滾動行限制存盤程序

下一篇：回傳前一周同月2年前的資料，日期范圍