RunningTotal column not working

Question

Post reply

RunningTotal column not working

luissantos

SSCertifiable

Points: 7391

April 11, 2021 at 10:15 pm

#3867902

Hello,

I need a little help with my query because they don´t work properly when i try to do a runningTotal column.

SQLQuery_bad and correct value on RunningTotal

This is my query:

DECLARE @ContaI  VARCHAR(15)
DECLARE @ContaF  VARCHAR(15)
DECLARE @DataI DATETIME
DECLARE @dataF DATETIME

SET @ContaI = '6221'
SET @ContaF = '6229999999'

SET @DataI = '20210401'
SET @DataF = '20210531'


SELECT '' [NrLanc], '' [Diario], '' dinome,
    'Saldo Inicial' as docnome, '' adoc,  ml.conta,'' descritivo,
    ISNULL(sum(edeb),0.00) as edeb, ISNULL(SUM(ecre),0.00) as ecre, '' data,
    SUM(SUM(ISNULL(edeb,0.00) - ISNULL(ecre,0.00))) OVER ( PARTITION BY ml.conta ORDER BY ml.conta) AS RunningTotal,
    '' as descricao,
    '' rubrica, '' cct,''  [desccct]

from ml (nolock) LEFT JOIN cu (Nolock) 
      ON ml.cct = cu.cct
where ml.conta>='62' and ml.conta<='6299999999' 
    AND  ML.DATA < @DataI AND DATEPART(yy,ml.data) = DATEPART(yy,@dataI)
    GROUP BY conta

    UNION ALL

SELECT CAST(dilno AS VARCHAR) [NrLanc],CAST(dino AS VARCHAR) [Diario],dinome,
    isnull((select top 1 docnome from dc where dc.docno=ml.doctipo),space(40)) as docnome,adoc,conta,descritivo,
    sum(edeb) as edeb,sum(ecre) as ecre,data,
    SUM(SUM(edeb - ecre)) OVER ( PARTITION BY ml.conta ORDER BY ml.conta) AS RunningTotal,
    isnull((select top 1 descricao from pc where pc.conta=ml.conta and pc.ano=year(ml.data)),space(35)) as descricao,
    ml.rubrica, cu.cct,cu.descricao [desccct]

from ml (nolock) LEFT JOIN cu (Nolock) 
      ON ml.cct = cu.cct
where ml.conta>='62' and ml.conta<='6299999999' 
    AND ML.DATA >= @DataI AND ML.DATA <= @DataF
group by data,conta,dino,dinome,dilno,doctipo,docno,adoc,descritivo, ml.rubrica,cu.cct,cu.descricao
  ORDER BY 6, 3, 1

Also, i provide this script based on my above query for creating Temporary table with values for testing purpose.

CREATE TABLE #tmpTable (
    NrLanc varchar(30),
    Diario varchar(30),
    dinome varchar(40),
    docnome varchar(40),
    adoc varchar(20),
    conta varchar(15),
    descritivo varchar(85),
    edeb decimal(38, 6),
    ecre decimal(38, 6),
    data datetime,
    RunningTotal decimal(38, 6),
    descricao varchar(55),
    rubrica varchar(20),
    cct varchar(20),
    desccct varchar(35))

INSERT #tmpTable VALUES
('','','','Saldo Inicial','','62213','',155.000000,0.000000,CONVERT(DATETIME, '1900-01-01 00:00:00.000', 121),155.000000,'','','',''),
('4000004','7','Compras','S/FACTURA','XPTO-2              ','62213','V/Factura XPTO-2 - A. ROCHA E FILHOS, LDA.                                           ',427.500000,0.000000,CONVERT(DATETIME, '2021-04-06 00:00:00.000', 121),427.500000,'Trabalhos Especializados - MN - Tx. Normal',' ','01.01.02','Administrativos'),
('','','','Saldo Inicial','','622643','',300.000000,0.000000,CONVERT(DATETIME, '1900-01-01 00:00:00.000', 121),300.000000,'','','',''),
('4000003','7','Compras','S/FACTURA','XPTO                ','622643','V/Factura XPTO - GARAGEM ALMEICESAR-REP.E COM.AUTO                                   ',155.000000,0.000000,CONVERT(DATETIME, '2021-04-06 00:00:00.000', 121),183.950000,'Conservação Viaturas - MN - Taxa Normal','61-PP-88','01.01.20','Viaturas Gerais'),
('4000003','7','Compras','S/FACTURA','XPTO                ','622643','V/Factura XPTO - GARAGEM ALMEICESAR-REP.E COM.AUTO                                   ',28.950000,0.000000,CONVERT(DATETIME, '2021-04-06 00:00:00.000', 121),183.950000,'Conservação Viaturas - MN - Taxa Normal','98-QI-09','01.01.20','Viaturas Gerais')

SELECT * FROM #tmpTable

DROP TABLE #tmpTable

I hope someone could give me the solution and what is incorrect.

Many thanks,

Best regards,

LS

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Thank you for the graphic that clearly demonstrates the problem and for the readily consumable test data, Luis. The following will do it for you. Details in the comments.

   WITH cteRunningTotal AS
(--==== Use only the columns necesary in the CTE and do the running total as a separate column
 SELECT  RunningTotal
        ,CorrectRunningTotal = SUM(edeb) OVER (PARTITION BY conta ORDER BY [data],rubrica )
   FROM #tmpTable
)--==== Update the cte to set the RunningTotal column to the correct calculation
     -- and that will update the RunningTotal column in the underlying table.
 UPDATE cteRunningTotal
    SET RunningTotal = CorrectRunningTotal
;
--===== Let's see the results...
 SELECT *
   FROM #tmpTable
  ORDER BY conta,[data],rubrica
;

As a bit of a sidebar, you were almost there. This doesn't need to be an UPDATE but I wanted to keep it all as simple as possible so you understand what I did with the SUM() OVER. You simply needed the right ORDER BY (I think... I don't have the tables to run the code you used).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

p.s. No long-winded lecture on the use of WITH(NOLOCK) or an ordinal column list in the ORDER BY. You've been around long enough to know the cautions and the reasons to not generally use either. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

luissantos SSCertifiable Points: 7391 More actions · Answer 3

Hello Jeff,

Thanks for your reply!

If I use the initial union query and put everything in a CTE, can I do an Update and then SELECT all fields?

I need a union query because in the 1st query I will look for the accumulated debit and credit values ??before the 1st date among the indicated accounts, while in the 2nd query I will look for all the debit and credit values ??between the indicated dates and accounts.

I just need to create a column that makes me the total per line, joining the values ??of the 1st and 2nd query grouping by the account.

Best regards,

LS

Thom A SSC Guru Points: 99126 More actions · Answer 4

Jeff Moden wrote:

p.s. No long-winded lecture on the use of WITH(NOLOCK) or an ordinal column list in the ORDER BY. You've been around long enough to know the cautions and the reasons to not generally use either. 😉

I sometimes find that the ones who have been around long enough are the one's that really need to be reminded of it's "problems". ?? I find many use it out of (bad) habit, rather than those who are new to the language and thus suffered from the wealth of misinformation about the table lock and it's magic "Go Faster" feature.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

luissantos wrote:

Hello Jeff,
Thanks for your reply!
If I use the initial union query and put everything in a CTE, can I do an Update and then SELECT all fields? I need a union query because in the 1st query I will look for the accumulated debit and credit values ??before the 1st date among the indicated accounts, while in the 2nd query I will look for all the debit and credit values ??between the indicated dates and accounts. I just need to create a column that makes me the total per line, joining the values ??of the 1st and 2nd query grouping by the account. Best regards,
LS

You don't need to do an UPDATE. I believe you just need to add the same ORDER BY to your existing SUM() OVER's as I have in the example code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Thom A wrote:

Jeff Moden wrote:
p.s. No long-winded lecture on the use of WITH(NOLOCK) or an ordinal column list in the ORDER BY. You've been around long enough to know the cautions and the reasons to not generally use either. 😉
I sometimes find that the ones who have been around long enough are the one's that really need to be reminded of it's "problems". ?? I find many use it out of (bad) habit, rather than those who are new to the language and thus suffered from the wealth of misinformation about the table lock and it's magic "Go Faster" feature.

Totally agreed. That's why I didn't do a "lecture" on it... just a reminder. Luis HAS been around long enough to know better. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Here is a quick example of using SUM() with an OVER clause to create a running total without having to UPDATE. It uses the data from your #tmpTable.

select conta, descritivo, edeb
,RunningTotal = SUM(edeb) OVER(ORDER BY conta, dinome, NrLanc ROWS UNBOUNDED PRECEDING)
from  #tmpTable
order by conta, dinome, NrLanc

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

The Dixie Flatline wrote:

Here is a quick example of using SUM() with an OVER clause to create a running total without having to UPDATE. It uses the data from your #tmpTable.
select conta, descritivo, edeb
,RunningTotal = SUM(edeb) OVER(ORDER BY conta, dinome, NrLanc ROWS UNBOUNDED PRECEDING)
from  #tmpTable
order by conta, dinome, NrLanc

That won't restart the running total when conta changes. You need to include the "PARTITION BY" to do that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

You are absolutely correct, Jeff.

select conta, descritivo, edeb
,RunningTotal = SUM(edeb) OVER(PARTITION BY conta ORDER BY dinome, NrLanc ROWS UNBOUNDED PRECEDING)
from  #tmpTable
order by conta, dinome, NrLanc

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills