Solved

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

Posted on 2008-10-28
7
186 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:Emoraes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
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

by:Emoraes
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;

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Emoraes
ID: 22869687
Hmmm interesting position.

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

Expert Comment

by:BrandonGalderisi
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

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

Computer101
EE Admin
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question