选择CASE WHEN返回的第一行

Question

这是针对SQL Server的。我有一个查询，试图查找某个时间点(最近交易日期前30天、最近交易日期后90天等(的账户总余额。我查询的表保存了一段时间内账户余额的"快照"，包括交易发生的金额和时间。

2021年9月29日2021年11月30日//tr>

PERSON	TOTALBALANCE	RCNTTRANS
Sarah	5000美元	2021年6月1日
Sarah	4500美元
Sarah	$7000
Joe	$90	2020年5月1日
Joe	$8000	2020年1月17日
Joe	2100美元	2021年2月28日

聚合和SUM每个CASE WHEN，并且在日期范围内。

create table #TEMP (
PERSON VARCHAR(30), 
TOTALBALANCE MONEY, 
RCNTTRANS DATE
)
insert into #TEMP (PERSON, TOTALBALANCE, RCNTTRANS) values
('Sarah',     $5000,  '2021-11-29'), 
('Sarah',     $4500,  '2021-12-01'), 
('Sarah',     $7000,  '2021-12-30'), 
('Joe',   $90,    '2020-08-28'), 
('Joe',   $8000,  '2021-02-01'), 
('Joe',   $2100,  '2021-02-28');

SELECT T.PERSON
, SUM(
CASE 
WHEN T.RCNTTRANS >= DATEADD(DAY, -30, M.TRANSACTIONDATE) 
AND T.RCNTTRANS <  M.TRANSACTIONDATE
THEN T.TOTALBALANCE END) AS BALANCE1
, SUM(
CASE 
WHEN T.RCNTTRANS >= DATEADD(DAY, -60, M.TRANSACTIONDATE) 
AND T.RCNTTRANS <  DATEADD(DAY, -30, M.TRANSACTIONDATE) 
THEN T.TOTALBALANCE END) AS BALANCE2
, SUM(
CASE 
WHEN T.RCNTTRANS >= DATEADD(DAY, -180, M.TRANSACTIONDATE) 
AND T.RCNTTRANS <  DATEADD(DAY, -60, M.TRANSACTIONDATE) 
THEN T.TOTALBALANCE END) AS BALANCE3
FROM #TEMP T
LEFT JOIN (
SELECT PERSON, MAX(RCNTTRANS) AS TRANSACTIONDATE
FROM #TEMP
GROUP BY PERSON
) M ON M.PERSON = T.PERSON
GROUP BY T.PERSON
ORDER BY T.PERSON DESC;

<blockquote\>

Sarah>

PERSON	BALANCE1	BALANCE2	BALANCE3
Joe	8000.0000	null	null

IF OBJECT_ID('tempdb..#TMP') IS NOT NULL
DROP TABLE #TMP;
CREATE TABLE #TMP (
PERSON VARCHAR(30), 
TOTALBALANCE MONEY, 
RCNTTRANS DATE
)
INSERT INTO #TMP (PERSON, TOTALBALANCE, RCNTTRANS) VALUES
('Sarah',   $5000,  '2021-01-06'), 
('Sarah',   $4500,  '2021-09-29'), 
('Sarah',   $7000,  '2021-11-30'), 
('Joe',     $90,    '2020-01-05'), 
('Joe',     $8000,  '2020-01-17'), 
('Joe',     $2100,  '2021-02-28');
DECLARE @TRANSACTIONDATE DATE = DATEFROMPARTS(2022,1,29); -- FOR TESTING
WITH CTE_BasicData
AS(
SELECT 
T.PERSON, 
T.TOTALBALANCE, 
T.RCNTTRANS,
DATEDIFF(day, @TRANSACTIONDATE, T.RCNTTRANS) AS [DAYS],
CASE 
WHEN DATEDIFF(day, @TRANSACTIONDATE, T.RCNTTRANS) <= -180 THEN '180 days'
WHEN DATEDIFF(day, @TRANSACTIONDATE, T.RCNTTRANS) <= -90 THEN '90 days'
WHEN DATEDIFF(day, @TRANSACTIONDATE, T.RCNTTRANS) <= -30 THEN '30 days'
ELSE NULL
END AS [Period]
FROM #TMP AS T
)
,CTE_Data
AS(
SELECT 
d.PERSON, 
d.TOTALBALANCE, 
d.RCNTTRANS,
d.[DAYS],
d.[Period],
DENSE_RANK() OVER(PARTITION BY d.PERSON, d.[Period] ORDER BY d.[DAYS] DESC) AS [Ranking]
FROM CTE_BasicData AS d
)
,CTE_Pivot
AS(
SELECT 
P.[PERSON]
,P.[30 days]
,P.[90 days]
,P.[180 days]
FROM (
SELECT 
d.PERSON, 
d.TOTALBALANCE, 
d.RCNTTRANS,
d.[DAYS],
d.[Period]
FROM CTE_Data AS d
WHERE (1=1)
AND d.Ranking = 1
) AS D
PIVOT(SUM(D.TOTALBALANCE) FOR D.[Period] IN([30 days],[90 days],[180 days])) AS P
)
--SELECT * FROM CTE_Data ORDER BY PERSON, RCNTTRANS; RETURN;
--SELECT * FROM CTE_Pivot ORDER BY PERSON; RETURN;
SELECT
d.[PERSON]
,SUM(d.[30 days]) AS [30 days]
,SUM(d.[90 days]) AS [90 days]
,SUM(d.[180 days]) AS [180 days]
FROM CTE_Pivot AS d
GROUP BY
d.[PERSON]