Go Premium for a chance to win a PS4. Enter to Win

x
Solved

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

Posted on 2008-10-28
Medium Priority
201 Views
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
``````
0
Question by:Emoraes
• 3
• 2

LVL 39

Expert Comment

ID: 22828278
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

Author Comment

ID: 22851454
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;
``````
0

LVL 39

Expert Comment

ID: 22851562
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

Author Comment

ID: 22869687
Hmmm interesting position.

I´ll re-analyze the query and post here the infos.
0

LVL 39

Expert Comment

ID: 23072919
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

LVL 1

Accepted Solution

Computer101 earned 0 total points
ID: 23112343
PAQed with points refunded (125)

Computer101
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month5 days, 23 hours left to enroll