T-SQL - Update rows with calculated values from previous rows-CodePudding

I need to create a dataset that calculates values using results from the previous row, but only the first row contains an actual value.

This is my dataset:

DROP TABLE IF EXISTS #tmp
CREATE TABLE #tmp (
    Date DATE
    , Month INT
    , Increment FLOAT
    , Results FLOAT
)

INSERT INTO #tmp(Date, Month, Increment, Results) 
VALUES
('7/1/2022', 0, 0.0027347877960046, 0.00439631056653702)
, ('7/1/2022', 1,   0.0332610867687839, NULL)
, ('7/1/2022', 2,   0.0541567096339919, NULL)
, ('7/1/2022', 3,   0.0534245249728661, NULL)
, ('7/1/2022', 4,   0.0497604938051764, NULL)
, ('7/1/2022', 5,   0.0448266874224477, NULL)
, ('7/1/2022', 6,   0.0637221774467554, NULL)
, ('7/1/2022', 7,   0.0953341922962425, NULL)
, ('7/1/2022', 8,   0.117940928214655,  NULL)
, ('7/1/2022', 9,   0.0955895317176205, NULL)
, ('6/1/2022',  0,  0.0027347877960046, 0.00439631056653702)
, ('6/1/2022',  1,  0.0332610867687839, 0.00752724387918406)
, ('6/1/2022',  2,  0.0541567096339919, NULL)
, ('6/1/2022',  3,  0.0534245249728661, NULL)
, ('6/1/2022',  4,  0.0497604938051764, NULL)
, ('6/1/2022',  5,  0.0448266874224477, NULL)
, ('6/1/2022',  6,  0.0637221774467554, NULL)
, ('6/1/2022',  7,  0.0953341922962425, NULL)
, ('6/1/2022',  8,  0.117940928214655,  NULL)
, ('6/1/2022',  9,  0.0955895317176205, NULL)

I need the Results = Previous Results Increment

Date    Month   Increment   Results
6/1/2022    0   0.002734788 0.004396311
6/1/2022    1   0.033261087 0.007527244
6/1/2022    2   0.05415671  0.061683954
6/1/2022    3   0.053424525 0.115108478
6/1/2022    4   0.049760494 0.164868972
6/1/2022    5   0.044826687 0.20969566
6/1/2022    6   0.063722177 0.273417837
6/1/2022    7   0.095334192 0.368752029
6/1/2022    8   0.117940928 0.486692958
6/1/2022    9   0.095589532 0.582282489
7/1/2022    0   0.002734788 0.004396311
7/1/2022    1   0.033261087 0.037657397
7/1/2022    2   0.05415671  0.091814107
7/1/2022    3   0.053424525 0.145238632
7/1/2022    4   0.049760494 0.194999126
7/1/2022    5   0.044826687 0.239825813
7/1/2022    6   0.063722177 0.303547991
7/1/2022    7   0.095334192 0.398882183
7/1/2022    8   0.117940928 0.516823111
7/1/2022    9   0.095589532 0.612412643

What's the best way to go about this?

CodePudding user response：

Seems like you could use a cumulative SUM here. As, however, you need to use the Results from Month 0 and ignore the value of Increment for Month 0, you also need to use conditional aggregation:

SELECT *,
       SUM(CASE MONTH WHEN 0 THEN Results END) OVER ()  
       SUM(CASE WHEN Month > 0 THEN Increment END) OVER (ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Results2
FROM #tmp;

db<>fiddle

CodePudding user response：

I found a solution using @larnu's row between method. Needed to split the data in two tables and union them together. Table one contains the actual Results column and table 2 contains the incrementally calculated Results.

DROP TABLE IF EXISTS #tmp;
CREATE TABLE #tmp (
    Date DATE
    , Month INT
    , Increment FLOAT
    , Results FLOAT
)
;
INSERT INTO #tmp(Date, Month, Increment, Results) 
VALUES
('7/1/2022', 0, 0.0027347877960046, 0.00439631056653702)
, ('7/1/2022', 1,   0.0332610867687839, NULL)
, ('7/1/2022', 2,   0.0541567096339919, NULL)
, ('7/1/2022', 3,   0.0534245249728661, NULL)
, ('7/1/2022', 4,   0.0497604938051764, NULL)
, ('7/1/2022', 5,   0.0448266874224477, NULL)
, ('7/1/2022', 6,   0.0637221774467554, NULL)
, ('7/1/2022', 7,   0.0953341922962425, NULL)
, ('7/1/2022', 8,   0.117940928214655,  NULL)
, ('7/1/2022', 9,   0.0955895317176205, NULL)
, ('6/1/2022',  0,  0.0027347877960046, 0.00439631056653702)
, ('6/1/2022',  1,  0.0332610867687839, 0.00752724387918406)
, ('6/1/2022',  2,  0.0541567096339919, NULL)
, ('6/1/2022',  3,  0.0534245249728661, NULL)
, ('6/1/2022',  4,  0.0497604938051764, NULL)
, ('6/1/2022',  5,  0.0448266874224477, NULL)
, ('6/1/2022',  6,  0.0637221774467554, NULL)
, ('6/1/2022',  7,  0.0953341922962425, NULL)
, ('6/1/2022',  8,  0.117940928214655,  NULL)
, ('6/1/2022',  9,  0.0955895317176205, NULL)
;

WITH mt AS (
    SELECT
        Date
        , MAX(Month) AS Month
    FROM #tmp
    WHERE Results IS NOT NULL
    GROUP BY Date
),
results AS (
    SELECT 
        t.*

        , SUM(CASE WHEN t.Results IS NOT NULL THEN t.Results END) OVER(PARTITION BY t.Date)  
         SUM(CASE WHEN t.Results IS NULL THEN t.Increment END) OVER (PARTITION BY t.Date ORDER BY t.Date, t.Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Results2
    FROM #tmp t
    INNER JOIN mt mt ON t.Date = mt.Date AND t.Month >= mt.Month
)
SELECT
    t.Date 
    , t.Month 
    , t.Increment 
    , t.Results
FROM #tmp t
LEFT JOIN mt mt ON t.Date = mt.Date 
WHERE t.Month <= mt.Month

UNION ALL

SELECT
    r.Date 
    , r.Month 
    , r.Increment
    , r.Results2 AS Results
FROM results r
WHERE r.results2 IS NOT NULL
ORDER BY Date, Month