Karl Karl - 15 days ago 8
SQL Question

SQL Recursive partial sum

I have two tables:

MasterTable

...
startSubNr
endSubNr
...


Step

...
nrFrom
nrTo
value
...


MasterTable contains for example:

startSubNr: 2
endSubNr: 16


Steps contains for example:

nrFrom: 2
nrTo: 19
value: 20

nrFrom: 1
nrTo: 2
value: 10

nrFrom: 19
nrTo: 5
value: 100

nrFrom: 5
nrTo: 16
value: 200

nrFrom: 4
nrTo: 5
value: 50


What I need is a sql query (which should work on SQL-Server and sqlite) to calculate the sum from a startnr to an endnr, where the number of steps can vary. The steps (from-to) are unique.

In this example it would have to sum up 20 (2-19), 100 (19-5) and 200 (5-16) while ignoring the values of 1-2 and 4-5.

Is there a way to do this in sql without having to use multiple queries?

Answer
    CREATE TABLE #Steps(nrFrom INT,nrTo INT ,value INT) DECLARE @Start INT = 2,@End INT = 16

    INSERT INTO #Steps(nrFrom ,nrTo ,value ) SELECT 2,19,20 UNION ALL SELECT 1,2,10 UNION ALL SELECT 19,5,100 UNION ALL SELECT 5,16,200 UNION ALL SELECT 4,5,50

    ;WITH _SumCTE (_From , _To , _Value )AS ( SELECT nrFrom ,nrTo,value FROM #Steps WHERE nrFrom = @Start UNION ALL SELECT _To , nrTo , value FROM #Steps JOIN _SumCTE ON _To = nrFrom and nrTo != @End

    )

    SELECT SUM(_Value) FROM _SumCTE