CODEWITHSUNDEEP
sql-server
deeSQL
deeSQL

Reputation: 5

current row + previous row in sql

I have a table like

beginv  nm  nm1 qty qty1    endinv  seq
0        a  ab1 5   7          0    1
0        a  ab2 15  7          0    2
0        a  ab3 11  9          0    3
0        a  ab3 11  9          0    4
0        a  ab3 11  9          0    5
0        b  ab2 10  14         0    1
0        b  ab3 11  9          0    2
0        c  ab1 11  9          0    1
0        c  ab2 15  9          0    2
0        c  ab3 15  9          0    3

Is there a way to show like below

beginv  nm  nm1 qty qty1    endinv  seq
0        a  ab1 5   7          7    1
0        a  ab2 15  7          15   2
0        a  ab3 11  9          17   3
0        a  ab3 11  9          17   4
0        a  ab3 11  9          17   5
0        b  ab2 10  14         14   1
0        b  ab3 11  9          16   2
0        c  ab1 11  9          9    1
0        c  ab2 15  9          15   2
0        c  ab3 15  9          21   3

if seq is 1 then endinv=qty1 else (previous)endinv+(qty-qty1) and same nm,nm1 is repeating then previous endiv (e.g. "a" with "ab3" repeats three times with same endinv value)

Upvotes: 0

Views: 41

Answers (1)

Thom A
Thom A

Reputation: 95571

Seems like you can do this with a couple of windowed conditional SUMs. Note, however, the results do not match that in your expected results, but I am assuming that that is because your expected results are wrong. You state the required logic is "(previous)endinv+(qty-qty1)", however, when seq has a value of 4 and nm a value of a, the value of endinv in 17. Applying your logic, however, you have 17 + 11 - 9 which equals 19 not 17.

SELECT beginv,
       nm,
       nm1,
       qty,
       qty1,
       SUM(CASE seq WHEN 1 THEN qty1 ELSE qty END) OVER (PARTITION BY nm ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -
       SUM(CASE seq WHEN 1 THEN 0 ELSE qty1 END) OVER (PARTITION BY nm ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS endinv,
       seq
FROM (VALUES(0,'a','ab1',5 ,7 ,0,1),
            (0,'a','ab2',15,7 ,0,2),
            (0,'a','ab3',11,9 ,0,3),
            (0,'a','ab3',11,9 ,0,4),
            (0,'a','ab3',11,9 ,0,5),
            (0,'b','ab2',10,14,0,1),
            (0,'b','ab3',11,9 ,0,2),
            (0,'c','ab1',11,9 ,0,1),
            (0,'c','ab2',15,9 ,0,2),
            (0,'c','ab3',15,9 ,0,3))V(beginv,nm,nm1,qty,qty1,endinv,seq);

db<>fiddle

Upvotes: 1

Related Questions