RunningTotal column not working
-
Hello,
I need a little help with my query because they don´t work properly when i try to do a runningTotal column.
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, 1Also, 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 #tmpTableI hope someone could give me the solution and what is incorrect.
Many thanks,
Best regards,
LS
-
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) -
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) -
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
- 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 - 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) - 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) -
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 - 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, NrLancThat 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) -
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply