How to Reference Calculated Value from previous line to find next calculated value

user_9104831 0
Viewed 80

Attempting to calculate a new field based off of values in table. Once First field is calculated need following rows the reference the previous value to begin next calculation.

Have attempted using Lag/Lead Functions as I will need to partition.

declare @test as table (
[Worked Hours] decimal(2,0),
[Sold Hours] decimal(3,0),
[Current Backlog] decimal(3,0),
[Product] nvarchar(10),
[Revenue Type] nvarchar(3),
[Month] date
)

INSERT INTO @TEST 
([Worked Hours], [Sold Hours], [Current Backlog], [Product], [Revenue Type], [Month])

VALUES
('10','150','50', 'Product', 'Revenue', '01-01-2019'),
('25','200','50', 'Product', 'Revenue', '02-01-2019'),
('15','175','50', 'Product', 'Revenue', '03-01-2019'),
('40','250','50', 'Product', 'Revenue', '04-01-2019')


select 

t.[Product],
t.[Revenue Type],
t.[Month],
t.[Worked Hours],
t.[Sold Hours],
t.[Current Backlog]

from @test as T

My expected Result would be a new column that would Use this Math.

T.[Current Backlog] - T.[Worked Hours] + T.[Sold Hours] = 'New Backlog' (For the First Line this would be 190).

The subsequent line would use the "New Backlog Value" (190) as substitution for T.[Current Backlog] in previous equation.

Overall Expected Results

Product Revenue 2019-01-01  10  150 50    190
Product Revenue 2019-02-01  25  200 50    365
Product Revenue 2019-03-01  15  175 50    525
Product Revenue 2019-04-01  40  250 50    735
tsql sql
2 Answers

I think you want a cumulative sum:

select t.*,
       [Current Backlog] + sum([Sold Hours] - [Worked Hours]) over (order by [Month])
from @test t;

Here is a db<>fiddle.

user_11440350

I would approach this with a recursive CTE. You need to rank and count the records, and then recursively process each record, assigning new backlog values as you go.

Consider:

WITH 
tab AS (
    SELECT
        t.*,
        ROW_NUMBER() OVER(ORDER BY [Month]) rn,
        COUNT(*) OVER() cnt
    FROM @test t
),
cte AS (
    SELECT 
        [Product], 
        [Revenue Type],
        [Month],
        [Worked Hours],
        [Sold Hours],
        CAST([Current Backlog] as integer) 
            - CAST([Worked Hours] as integer) 
            + CAST([Sold Hours] as integer) [New Backlog],
        cnt,
        1 n
    FROM tab t
    WHERE t.rn = 1
    UNION ALL
    SELECT 
        t.[Product],
        t.[Revenue Type],
        t.[Month],
        t.[Worked Hours],
        t.[Sold Hours],
        c.[New Backlog] 
            - CAST(t.[Worked Hours] AS integer) 
            + CAST(t.[Sold Hours] AS integer) [New Backlog],
            t.cnt,
            c.n + 1
    FROM tab t
    INNER JOIN cte c ON t.rn = c.n + 1
    WHERE c.n <= t.cnt
)
SELECT 
    [Product],
    [Revenue Type],
    [Month],
    [Worked Hours],
    [Sold Hours],
    [New Backlog]
FROM cte;

Demo on DB Fiddle:

Product | Revenue Type | Month               | Worked Hours | Sold Hours | New Backlog
:------ | :----------- | :------------------ | :----------- | :--------- | ----------:
BBCRM   | T&M          | 01/01/2019 00:00:00 | 10           | 150        |         190
BBCRM   | T&M          | 01/02/2019 00:00:00 | 25           | 200        |         365
BBCRM   | T&M          | 01/03/2019 00:00:00 | 15           | 175        |         525
BBCRM   | T&M          | 01/04/2019 00:00:00 | 40           | 250        |         735
user_106767160
Related