This is for SQL Server. I have a query that's trying to find the total balance in an account at a certain point in time (30 days before most recent transaction date, 90 days before most recent transaction date, etc). The table I'm querying keeps 'snapshots' of account balances over time with the amount and the time in which the transaction occurred.

I figured I could use a case statement to get the total balance at any date less than the most recent transaction date minus however many days away I'm looking for. However, this returns a row for every previous RCNTTRANS date. Is there a way to select only the first row that's returned?

SELECT 
    ,T.PERSON
    ,CASE WHEN T.TRANSACTIONDATE <= DATEADD(DAY, -30, T.RCNTTRANS) THEN T.TOTALBALANCE ELSE 0 END AS TEST
    ,CASE WHEN T.TRANSACTIONDATE <= DATEADD(DAY, -90, T.RCNTTRANS) THEN T.TOTALBALANCE ELSE 0 END AS TEST2
    ,CASE WHEN T.TRANSACTIONDATE <= DATEADD(DAY, -180, T.RCNTTRANS) THEN T.TOTALBALANCE ELSE 0 END AS TEST3
FROM #TEMP T

I tried COALESCE, but that didn't seem to work. I also tried FIRST_VALUE, but that didn't seem to work either. I could possibly have been using them incorrectly, though.

Expected Results

CodePudding user response：

Aggregate and SUM each CASE WHEN, and within date ranges.

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;

db<>fiddle here

CodePudding user response：

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]