October 30, 2008 at 5:11 pm
Hello comunity
i have the following TSQL cursor and i need to reset the total when
@conta = @contaactual and @nrter = @nrterActual
@conta = is my accounting type 211 or 212 or 213
@nrter is my client number
if my @conta = 211 and my @nrter =10075 then @total sum each line, but
if my @conta = 212 and my @nter =10075 then @total must reset to zero and must sum all documents with the same @conta and @nter
but also
if change @nter and @conta is 211 the @total must be reset to zero to start the sum of my new client with the @conta =211 and so on
i join my script above
Could anyone help me
Many thanks
Luis Santos
DECLARE @VENDEDOR VARCHAR(10)
SET @VENDEDOR = #1# -- is my vendor
DECLARE @tbl TABLE (Linha int NOT NULL IDENTITY (1, 1), Conta varchar(10), nrter Bigint,nome varchar (60),Telefone varchar(25), Fax varchar(25),
Vendedor varchar(60), DataEmissao datetime, DataVenc datetime,
TpDoc varchar(25), Doc varchar(16), [A30Dias] numeric(18,2),
[B60Dias] numeric(18,2), [C90Dias] numeric(18,2), [DMais90Dias] numeric(18,2), [FNVencidos] numeric(18,2),
Total numeric(18,2), Acum numeric(18,2), Dias bigint, ccstamp varchar(25))
INSERT INTO @tbl (Conta, nrter, nome, Telefone, Fax, Vendedor, DataEmissao, DataVenc,
TpDoc, Doc, [A30Dias], [B60Dias], [C90Dias], [DMais90Dias], [FNVencidos],
Total, Dias, ccstamp)
select
'211' as 'Conta', cc.no as 'nrter',cc.nome as 'Nome',
(select telefone from cl where cl.no=cc.no) as Telefone,
(select fax from cl where cl.no=cc.no) as Fax,
ltrim(rtrim(cast(ltrim(rtrim(cl.vendedor)) as char) +'-'+cl.vendnm)) as 'Vendedor',
datalc as 'DataEmissão',
dataven as 'DataVenc',
cmdesc as 'TpDoc',
cast(nrdoc as varchar) as 'ºDoc',
sum(case when (edeb-edebf)<>0 and datediff(dd,cc.dataven,getdate()) between 0 and 30 then (edeb-edebf) else case when -(ecred-ecredf)<>0 and datediff(dd,cc.dataven,getdate()) between 0 and 30 then
-(ecred-ecredf) else 0 end end) as 'A.0-30Dias',
sum(case when (edeb-edebf)<>0 and datediff(dd,cc.dataven,getdate()) between 31 and 60 then (edeb-edebf) else case when -(ecred-ecredf)<>0 and datediff(dd,cc.dataven,getdate()) between 31 and 60 then
-(ecred-ecredf) else 0 end end) as 'B.31-60Dias',
sum(case when (edeb-edebf)<>0 and datediff(dd,cc.dataven,getdate()) between 61 and 90 then (edeb-edebf) else case when -(ecred-ecredf)<>0 and datediff(dd,cc.dataven,getdate()) between 61 and 90 then
-(ecred-ecredf) else 0 end end) as 'C.61-90Dias',
sum(case when (edeb-edebf)<>0 and datediff(dd,cc.dataven,getdate())>91 then (edeb-edebf) else case when -(ecred-ecredf)<>0 and datediff(dd,cc.dataven,getdate()) >91 then
-(ecred-ecredf) else 0 end end) as 'D.+91Dias',
sum(case when (edeb-edebf)<>0 and datediff(dd,cc.dataven,getdate()) 0 and datediff(dd,cc.dataven,getdate())<=0 then -(ecred-ecredf) else 0 end end) as 'N/Vencidos',
sum((edeb-edebf)-(ecred-ecredf)) as 'Total',
dias=datediff(dd,cc.dataven,getdate()),
cc.ccstamp as 'Index'
from cc (nolock) , cl (nolock)
where cc.no=cl.no and (case when cc.moeda='PTE ou EURO' or cc.moeda=space(11) then abs((cc.edeb-cc.edebf)-(cc.ecred-cc.ecredf)) else abs((cc.debm-cc.debfm)-(cc.credm-cc.credfm)) end) > (case when cc.moeda='PTE ou EURO' or cc.moeda=space(11) then 0.010000 else 0 end)
and cl.vendedor like substring(#1#,1,3)
group by cc.no, cl.vendedor,cl.vendnm,cc.datalc,cc.dataven,cc.cmdesc,cc.nrdoc,cc.ccstamp,cc.nome
union all
select
'212' as 'Conta', lr.no as 'nrter',lr.nome as 'Nome',
(select telefone from cl where cl.no=lr.no) as Telefone,
(select fax from cl where cl.no=lr.no) as Fax,
(select ltrim(rtrim(cast(ltrim(rtrim(cl.vendedor)) as char) +'-'+cl.vendnm)) from cl where cl.no=lr.no) as 'Vendedor',
EDATA as 'DataEmissão',
VDATA as 'DataVenc',
(case when lr.u_pagchpd= 0 and lr.u_pagpagar = 0 then 'LETRA' else '' end ) as 'TpTDoc',
(case when lr.u_pagchpd= 0 and lr.u_pagpagar = 0 then cast(LETRA as varchar) else '' end ) as 'NºDoc',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate()) between 0 and 30 then (evalor) else 0 end) as 'A.0-30Dias',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate()) between 31 and 60 then (evalor) else 0 end) as 'B.31-60Dias',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate()) between 61 and 90 then (evalor) else 0 end) as 'C.61-90Dias',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate())>91 then evalor else 0 end) as 'D.+91Dias',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate())<=0 then evalor else 0 end) as 'N/Vencidos',
sum(evalor) as 'Total',
Dias=datediff(dd,lr.vdata,getdate()),
lrstamp as 'Index'
from lr (nolock), cl (nolock) where lr.no=cl.no and lr.estab=0 and eregvalor = 0 and lr.u_pagchpd=0 and lr.u_pagpagar=0
and cl.vendedor like substring(#1#,1,3)
group by lr.no,lr.edata,lr.vdata,lr.u_pagchpd,lr.u_pagpagar,lr.letra,lr.lrstamp,lr.nome
union all
select
'213' as 'Conta', lr.no as 'nrter',lr.nome as 'Nome',
(select telefone from cl where cl.no=lr.no) as Telefone,
(select fax from cl where cl.no=lr.no) as Fax,
(select ltrim(rtrim(cast(ltrim(rtrim(cl.vendedor)) as char) +'-'+vendnm)) from cl where cl.no=lr.no) as 'Vendedor',
EDATA as 'DataEmissão',
VDATA as 'DataVenc',
(case when lr.u_pagchpd= 1 and lr.u_pagpagar = 0 then 'CH.PD.' else '' end ) as 'TpTDoc',
(case when lr.u_pagchpd= 1 and lr.u_pagpagar = 0 then lr.u_nrchpd else '' end ) as 'NºDoc',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate()) between 0 and 30 then (evalor) else 0 end) as 'A.0-30Dias',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate()) between 31 and 60 then (evalor) else 0 end) as 'B.31-60Dias',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate()) between 61 and 90 then (evalor) else 0 end) as 'C.61-90Dias',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate())>91 then evalor else 0 end) as 'D.+91Dias',
sum(case when (evalor)<>0 and datediff(dd,lr.vdata,getdate())<=0 then evalor else 0 end) as 'N/Vencidos',
sum(evalor) as 'Total',
dias=datediff(dd,lr.vdata,getdate()),
lrstamp as 'Index'
from lr (nolock) , cl (nolock) where lr.no=cl.no and lr.estab=0 and eregvalor = 0 and lr.u_pagchpd=1
and cl.vendedor like substring(#1#,1,3)
group by lr.no,lr.edata,lr.vdata,lr.u_pagchpd,lr.u_pagpagar,lr.letra,lr.lrstamp,lr.nome,lr.u_nrchpd
order by 1,7
--SELECT * FROM @tbl
DECLARE @linha int, @conta varchar(10), @nrter int , @total numeric(18,2)
DECLARE @runtotal numeric(18,2), @contaactual varchar(10), @nrterActual int
DECLARE cur1 CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT Linha, Conta, Total FROM @tbl
OPEN cur1
FETCH NEXT FROM cur1 INTO @linha, @conta, @total
SELECT @runtotal = 0, @contaactual = @conta ,@nrteractual =@nrter
WHILE @@FETCH_STATUS = 0
BEGIN
IF @conta = @contaactual and @nrter = @nrterActual
SET @runtotal = @runtotal + @total
ELSE
BEGIN
SET @runtotal = @total
SET @contaactual = @conta
SET @nrterActual = @nrter
END
UPDATE @tbl SET Acum = @runtotal
WHERE Linha = @linha
FETCH NEXT FROM cur1 INTO @linha, @conta,@total
END
SELECT * from @tbl
CLOSE cur1
DEALLOCATE cur1
October 30, 2008 at 10:28 pm
luissantos,
If you can post some table Definition / Sample Data for this (an example of how to do this is in my signature), we can help you write something that gets rid of your cursor. (And runs in a fraction of the time) The method will be based on Jeff Moden's Running Total technique. Go read this article and maybe even give it a shot yourself. If you get stuck, we're here to help.
Standard Running Total Technique:
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
October 31, 2008 at 8:05 pm
Heh... I've just gotta agree with that! 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply