select value from row-1 MS SQL

I have simple code

select duguje as 'Duguje',
potrazuje as 'Potražuje',
(duguje-potrazuje) as saldo
from dbo.nalog_knjizenje_stavke

Result is

That is ok, I need to repair my code to be last column 'saldo' next values:

saldo(last value) + Duguje - Potrazuje = need to be result

Thak you

You can try the following query.

create table MyTable (Id int identity (1, 1)
, ColA decimal(18, 2)
, ColB decimal(18, 2)
, ColC decimal(18, 2))
insert into MyTable Values
(30.23, 0.00, 30.23),
(23.32, 0.00, 23.32),
(23.32, 0.00, 23.32),
(15.51, 0.00, 15.51),
(15.51, 0.00, 15.51),
(23.32, 0.00, 23.32),
(7.69, 0.00, 7.69)
select ColA
, ColB
, ColC
, SUM(ColC) OVER(ORDER BY Id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Needed
from MyTable

select ColA
, ColB
, ColC
, (SELECT SUM(ColC) FROM MyTable t2 WHERE t2.Id <= MyTable.Id) AS Needed
from MyTable

If your table has not any identity key then generate a row number like serial number for each row i.e. 1, 2, 3, 4, .. and join the table based on that row number as shown below:

;with cte as(
Select *
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
from MyTable
)
select ColA
, ColB
, ColC
, (SELECT SUM(ColC) FROM cte t2 WHERE t2.RN <= cte.RN) AS Needed
from cte

Live demo link

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f252dea2185004aa6b857754f93641ae

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar6y ago
Accepted answer
You can try the following query.
create table MyTable (Id int identity (1, 1)
, ColA decimal(18, 2)
, ColB decimal(18, 2)
, ColC decimal(18, 2))

insert into MyTable Values
(30.23, 0.00, 30.23),
(23.32, 0.00, 23.32),
(23.32, 0.00, 23.32),
(15.51, 0.00, 15.51),
(15.51, 0.00, 15.51),
(23.32, 0.00, 23.32),
(7.69, 0.00, 7.69)

select ColA
, ColB
, ColC
,  SUM(ColC) OVER(ORDER BY Id
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Needed
from MyTable
select ColA
, ColB
, ColC
, (SELECT SUM(ColC) FROM MyTable t2 WHERE t2.Id <= MyTable.Id) AS Needed
from MyTable
If your table has not any identity key then generate a row number like serial number for each row i.e. 1, 2, 3, 4, .. and join the table based on that row number as shown below:
;with cte as(
  Select *
  , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
  from MyTable
)
select ColA
, ColB
, ColC
, (SELECT SUM(ColC) FROM cte t2 WHERE t2.RN <= cte.RN) AS Needed
from cte
Live demo link
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f252dea2185004aa6b857754f93641ae
Goran Bibic6y ago
Thank you so much