September 29, 2010 at 3:57 pm
Hello comunity
I have a problem with this following cursor, because my column SALDO (RunningTotal) dont reset when @nrcli and @vendedor (nrcli is customer number and @vendedor is Sales member name)
changed.
this is my script :
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
select cc.vendnm as 'Vendedor',cC.no as 'NrCli',cc.nome as 'Cliente',cast(cC.estab as varchar) as 'Estab',
convert(char(10),datalc,105) as 'DataDoc',convert(char(10),dataven,105) as 'DataVenc',cc.cmdesc as 'TpDoc',
cast(cc.nrdoc as varchar) as 'NrDoc',
cc.moeda as 'Moeda',
edeb as'ValorOriginalFact',
-ecred as 'ValorOriginalNCred', sum(edeb-edebf) as 'SldDevedor',-sum(ecred-ecredf)as 'SldCredor',
'Idade'= convert(char(8),datediff(dd,cc.datalc,getdate()),112)
from cc (nolock)
--inner join cl (nolock) on cl.no=cc.no
--and cl.estab=cc.estab AND CL.VENDEDOR = CC.VENDEDOR
--and
WHERE
convert(char(8),cc.datalc,112)>= '20100101'
and convert(char(8),cc.datalc,112)<= '20101231'
and cmdesc not like 'N/Rec%'
--and cl.no = 211
and ((CC.VENDEDOR = 1))
group by cC.no,cc.nome,cC.estab,datalc,dataven,cc.moeda,cc.nrdoc,cc.cmdesc,cc.vendnm,cc.edeb,cc.ecred
order by cC.no,datalc,nrdoc
OPEN RunningTotalCursor
--I am not sure of the datatypes you need, so change accordingly
DECLARE @vendedor varchar(20)
DECLARE @Nrcli INT
DECLARE @cliente varchar(60)
DECLARE @estab varchar (3)
DECLARE @datadoc varchar(10)
DECLARE @datavenc varchar(10)
DECLARE @tpdoc varchar(20)
DECLARE @Nrdoc varchar(20)
DECLARE @Moeda varchar(20)
DECLARE @ValorOriginalFact numeric(10,2)
DECLARE @ValorOriginalNCred numeric(10,2)
DECLARE @SldDevedor numeric(10,2)
DECLARE @SldCredor numeric(10,2)
DECLARE @Idade numeric(10,0)
--set starting value of the running total
DECLARE @RunningTotal numeric(10,2)
DECLARE @NrCliactual INT
DECLARE @VENDEDORACTUAL VARCHAR(20)
SET @RunningTotal = 0
SET @NrCliactual = 0
SET @VENDEDORACTUAL = ''
--hold our results data
DECLARE @Results TABLE
(
vendedor varchar(20),
Nrcli INT,
cliente varchar(60) ,
estab varchar (3),
datadoc varchar(10),
datavenc varchar(10),
tpdoc varchar(20),
Nrdoc varchar(20),
Moeda varchar(20),
ValorOriginalFact numeric(10,2),
ValorOriginalNCred numeric(10,2),
SldDevedor numeric(10,2),
SldCredor numeric(10,2),
Idade numeric(10,0),
RunningTotal numeric(10,2)
)
FETCH NEXT FROM RunningTotalCursor
INTO @vendedor,@Nrcli, @cliente, @estab, @datadoc,@datavenc, @tpdoc, @Nrdoc,@moeda, @ValorOriginalFact,
@ValorOriginalNCred, @SldDevedor, @SldCredor, @Idade
WHILE @@FETCH_STATUS = 0
BEGIN
--debit is SldDevedor and Credit is SldCredor
if @nrcli <> @NrCliactual AND RTRIM(@vendedor) <> RTRIM(@VENDEDORACTUAL)
BEGIN
SET @RunningTotal = 0
SET @NrCliactual= @nrcli
SET @VENDEDORACTUAL = RTRIM(@vendedor)
END
SET @RunningTotal = @RunningTotal + (@SldDevedor-@SldCredor)
--insert values into the results table
INSERT @Results
VALUES (@vendedor,@Nrcli, @cliente, @estab, @datadoc,@datavenc, @tpdoc, @Nrdoc,@moeda, @ValorOriginalFact,
@ValorOriginalNCred, @SldDevedor, @SldCredor, @Idade, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @vendedor,@Nrcli, @cliente, @estab, @datadoc,@datavenc, @tpdoc, @Nrdoc,@moeda, @ValorOriginalFact,
@ValorOriginalNCred, @SldDevedor, @SldCredor, @Idade
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
--select all records from the results table variable
SELECT vendedor as 'Vendedor', nrcli as 'NrCli',cliente as 'Cliente',estab as 'Estab.',
datadoc as 'DataDoc',datavenc as 'Dataven',tpdoc as 'Tipodoc',Nrdoc as 'NrDoc',moeda as 'Moeda',
ValorOriginalFact as 'ValorOriginalFact',ValorOriginalNCred as 'ValorOriginalNCred',
Slddevedor as 'SaldoDevedor', SldCredor as 'SaldoCredor',Idade as 'Idade', RunningTotal as 'Saldo'
FROM @Results
ORDER BY nrcli,vendedor
Could someone explain why ?
Many thanks
Luis Santos
September 29, 2010 at 6:59 pm
luissantos (9/29/2010)
Hello comunityI have a problem with this following cursor, because my column SALDO (RunningTotal) dont reset when @nrcli and @vendedor (nrcli is customer number and @vendedor is Sales member name)
changed.
this is my script :
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
select cc.vendnm as 'Vendedor',cC.no as 'NrCli',cc.nome as 'Cliente',cast(cC.estab as varchar) as 'Estab',
convert(char(10),datalc,105) as 'DataDoc',convert(char(10),dataven,105) as 'DataVenc',cc.cmdesc as 'TpDoc',
cast(cc.nrdoc as varchar) as 'NrDoc',
cc.moeda as 'Moeda',
edeb as'ValorOriginalFact',
-ecred as 'ValorOriginalNCred', sum(edeb-edebf) as 'SldDevedor',-sum(ecred-ecredf)as 'SldCredor',
'Idade'= convert(char(8),datediff(dd,cc.datalc,getdate()),112)
from cc (nolock)
--inner join cl (nolock) on cl.no=cc.no
--and cl.estab=cc.estab AND CL.VENDEDOR = CC.VENDEDOR
--and
WHERE
convert(char(8),cc.datalc,112)>= '20100101'
and convert(char(8),cc.datalc,112)<= '20101231'
and cmdesc not like 'N/Rec%'
--and cl.no = 211
and ((CC.VENDEDOR = 1))
group by cC.no,cc.nome,cC.estab,datalc,dataven,cc.moeda,cc.nrdoc,cc.cmdesc,cc.vendnm,cc.edeb,cc.ecred
order by cC.no,datalc,nrdoc
OPEN RunningTotalCursor
--I am not sure of the datatypes you need, so change accordingly
DECLARE @vendedor varchar(20)
DECLARE @Nrcli INT
DECLARE @cliente varchar(60)
DECLARE @estab varchar (3)
DECLARE @datadoc varchar(10)
DECLARE @datavenc varchar(10)
DECLARE @tpdoc varchar(20)
DECLARE @Nrdoc varchar(20)
DECLARE @Moeda varchar(20)
DECLARE @ValorOriginalFact numeric(10,2)
DECLARE @ValorOriginalNCred numeric(10,2)
DECLARE @SldDevedor numeric(10,2)
DECLARE @SldCredor numeric(10,2)
DECLARE @Idade numeric(10,0)
--set starting value of the running total
DECLARE @RunningTotal numeric(10,2)
DECLARE @NrCliactual INT
DECLARE @VENDEDORACTUAL VARCHAR(20)
SET @RunningTotal = 0
SET @NrCliactual = 0
SET @VENDEDORACTUAL = ''
--hold our results data
DECLARE @Results TABLE
(
vendedor varchar(20),
Nrcli INT,
cliente varchar(60) ,
estab varchar (3),
datadoc varchar(10),
datavenc varchar(10),
tpdoc varchar(20),
Nrdoc varchar(20),
Moeda varchar(20),
ValorOriginalFact numeric(10,2),
ValorOriginalNCred numeric(10,2),
SldDevedor numeric(10,2),
SldCredor numeric(10,2),
Idade numeric(10,0),
RunningTotal numeric(10,2)
)
FETCH NEXT FROM RunningTotalCursor
INTO @vendedor,@Nrcli, @cliente, @estab, @datadoc,@datavenc, @tpdoc, @Nrdoc,@moeda, @ValorOriginalFact,
@ValorOriginalNCred, @SldDevedor, @SldCredor, @Idade
WHILE @@FETCH_STATUS = 0
BEGIN
--debit is SldDevedor and Credit is SldCredor
if @nrcli <> @NrCliactual AND RTRIM(@vendedor) <> RTRIM(@VENDEDORACTUAL)
BEGIN
SET @RunningTotal = 0
SET @NrCliactual= @nrcli
SET @VENDEDORACTUAL = RTRIM(@vendedor)
END
SET @RunningTotal = @RunningTotal + (@SldDevedor-@SldCredor)
--insert values into the results table
INSERT @Results
VALUES (@vendedor,@Nrcli, @cliente, @estab, @datadoc,@datavenc, @tpdoc, @Nrdoc,@moeda, @ValorOriginalFact,
@ValorOriginalNCred, @SldDevedor, @SldCredor, @Idade, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @vendedor,@Nrcli, @cliente, @estab, @datadoc,@datavenc, @tpdoc, @Nrdoc,@moeda, @ValorOriginalFact,
@ValorOriginalNCred, @SldDevedor, @SldCredor, @Idade
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
--select all records from the results table variable
SELECT vendedor as 'Vendedor', nrcli as 'NrCli',cliente as 'Cliente',estab as 'Estab.',
datadoc as 'DataDoc',datavenc as 'Dataven',tpdoc as 'Tipodoc',Nrdoc as 'NrDoc',moeda as 'Moeda',
ValorOriginalFact as 'ValorOriginalFact',ValorOriginalNCred as 'ValorOriginalNCred',
Slddevedor as 'SaldoDevedor', SldCredor as 'SaldoCredor',Idade as 'Idade', RunningTotal as 'Saldo'
FROM @Results
ORDER BY nrcli,vendedor
Could someone explain why ?
Many thanks
Luis Santos
My recommendation would be to start with a smaller known dataset and test your code until you get it right. Yeah... I know... not what you wanted to hear here but it'll make for better code in the future. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 30, 2010 at 8:24 am
First and foremost, someone is going to point out to you that you probably dont need a cursor here, so it may as well be me.
Second, its tough to tell eyeballing your script, but there seem to be three logical problems.
First:
if @nrcli <> @NrCliactual AND RTRIM(@vendedor) <> RTRIM(@VENDEDORACTUAL)
This line requires that BOTH of these values are changed from the previous record, or it doesnt reset things. Presumably you want to reset the total if EITHER changes, meaning you want an OR not and AND.
Second (and related), you only set @NrCliactual and @VENDEDORACTUAL when the if condition is true. Since they are meant to represent the previous record of each field, you should set them all the time. If you if statement was working this wouldnt matter, but as is, if @nrcli changes, but @vendedor does not, the actuals aren't getting updated, making the comparison illegitimate for the next row.
Third, if you want to reset the running total every time these columns change, then you should be ordering by both of these columns (here it looks like you only order by one).
September 30, 2010 at 11:07 pm
Nevyn (9/30/2010)
First and foremost, someone is going to point out to you that you probably dont need a cursor here, so it may as well be me.
How would you do a running total without a cursor? Please don't say "While Loop" because that's just as bad. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2010 at 8:10 am
October 2, 2010 at 12:17 am
Nevyn (10/1/2010)
Well, I've never tried it myself, so I'd probably refer to this article[/url] some guy wrote.:Whistling:Or I'd do the running total in whatever my frontend was.
Heh... ok... now I get it when you said someone was going to recommend avoiding the cursor altogether :-D. I thought maybe you had another way of doing it in T-SQL that I wasn't aware of.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply