时间戳

首页 > 解决方案 > 返回每个月填充空值的最新值

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

问题描述

在 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

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

标签： sqlsql-servertsql

解决方案

首先，我得到每个 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;

推荐阅读