How to add the preview value of a cell to the actual cell value?

I want to make a subtotal colum that would consist of the last value from the subtotal plus the actual row value from 'qtd_venda'.

Example:

qtd_venda , subtotal

20, 20
20, 40
20, 60
35, 95
10, 105

Thanks.
SELECT NF.nro_nota, VI.qtd_venda
FROM SCGE_venda_item_tb VI 
    LEFT JOIN SCGE_venda_tb V ON V.id_venda = VI.id_venda
    LEFT JOIN SCGE_nota_fiscal_tb NF ON NF.id_venda = VI.id_venda

Open in new window

EmoraesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
So a running total?

The problem I can see with your query is that your subtotal comes from VI but your first column comes from NF which is in the right part of a left outer join.  You will end up with ALL items without nf.nro_nota being grouped into one.

I need to know the order in which your records appear.  You can't give a running total without knowing the order in which the records should appear.  Is there a date column, or an ID column somewhere?  

It's an easy query once I have that.

0
EmoraesAuthor Commented:
Actually I solved this problem creating a function to calc on-the-fly the running total. Don´t know if it is the best solution or the best one, but solved my problem.

Oh and the order is by nro_nota
GO
CREATE FUNCTION dbo.Calc_Subtotal (@nNota int)
RETURNS int
 BEGIN
   RETURN (
   SELECT SUM(VI.qtd_venda)
     FROM SCGE_venda_item_tb VI 
       LEFT JOIN SCGE_venda_tb V ON V.id_venda = VI.id_venda
       LEFT JOIN SCGE_nota_fiscal_tb NF ON NF.id_venda = VI.id_venda
   WHERE NF.nro_nota <= @nNota)
 END;
GO
SELECT NF.nro_nota, VI.qtd_venda, dbo.Calc_Subtotal(NF.nro_nota) as Subtotal
FROM SCGE_venda_item_tb VI 
    LEFT JOIN SCGE_venda_tb V ON V.id_venda = VI.id_venda
    LEFT JOIN SCGE_nota_fiscal_tb NF ON NF.id_venda = VI.id_venda
GO
DROP FUNCTION Calc_Subtotal;

Open in new window

0
BrandonGalderisiCommented:
Will that really do what you want?  You have no filtering to say what records in vi or v should be summed.  This will sum all records with a lower scge_nota_fiscal_tb.nro_nota value.

This is along the lines of what I was going to do, without a function though, but I needed to know what field was the "sorting field" which appears to be scge_nota_fiscal_tb.nro_nota.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

EmoraesAuthor Commented:
Hmmm interesting position.

I´ll re-analyze the query and post here the infos.
0
BrandonGalderisiCommented:
I think that http:#22851562 points out that the query has a problem in that it's not limiting to the original join criteria.  The author never posted back about this, but I'm sure of it.
0
Computer101Commented:
PAQed with points refunded (125)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.