April 28, 2011 at 11:18 am
Hello comunity
I have build this script above to return the account movment for determinate customer, the problem is the column RunningTotal appears without values, someone could help me.
The script is :
DECLARE @Subconta VARCHAR(60)
DECLARE @CONTAINI VARCHAR(15)
DECLARE @CONTAFIM VARCHAR(15)
DECLARE @MESINI INT
DECLARE @MESFIM INT
DECLARE @ANO INT
DECLARE @unidF varchar(30)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @CodUnidF varchar(2)
set @subconta = LEFT('14770',5)
set @mesini = 0
set @mesfim = 12
set @ano = 2011
set @containi = Rtrim('2111000001')
set @contafim = Rtrim('2131100001')
set @unidF = LEFT('DBFRA - Haste-Aveiro',5)
set @CodUnidF = LEFT('51(DBFRA)',2)
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TMPCLICCO') AND type = (N'U'))
DROP TABLE TMPCLICCO
SET @SQL = N'
SELECT conta, docnome,u_subconta, data, mlstamp,
dinome, adoc, dilno, edeb, ecre,
(select nome from '+ @unidF + '..cl CL (nolock) where cl.no = CAST(ml.u_subconta AS INT)) NomeCli
INTO TMPCLICCO
FROM dbhaste..ml ML where ((ml.conta not like ''243%'' and ml.conta not like ''22%'')
and (ml.conta between ''' + @containi + ''' and ''' + @contafim + '''))
and month(ml.data) >= ' + convert(varchar,@mesini) + ' and month(ml.data) <= ' + convert(varchar,@mesfim) + '
and u_subconta like substring( '''+ convert(varchar,@Subconta)+ ''',1,5)
and month(ml.data) >= 0
and year(ml.data) = ' + convert(varchar,@ano) + '
AND RIGHT(RTRIM(ML.INTID),2) = ' + CONVERT(VARCHAR,@CodUnidF ) + '
--ADD WHERE AND ORDER BY CLAUSE HERE IF NEEDED
group by conta,docnome,u_subconta,data,
dinome, adoc, dilno, edeb, ecre ,mlstamp
ORDER BY conta, u_subconta,data asc ' --« Fim de N
EXEC sp_executesql @sql
--print @sql
SELECT * FROM TMPCLICCO
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
select conta, docnome,u_subconta, data, mlstamp,
dinome, adoc, dilno, edeb, ecre, NomeCli
from TMPCLICCO
OPEN RunningTotalCursor
--I am not sure of the datatypes you need, so change accordingly
DECLARE @conta varchar(20)
DECLARE @docnome varchar (40)
DECLARE @u_subconta varchar(20)
DECLARE @data datetime
DECLARE @mlstamp varchar(25)
DECLARE @dinome varchar(20)
DECLARE @adoc varchar(20)
DECLARE @dilno varchar(20)
DECLARE @edeb numeric(10,2)
DECLARE @ecre numeric(10,2)
DECLARE @NomeCli varchar(60)
--set starting value of the running total
DECLARE @RunningTotal numeric(10,2)
DECLARE @subcontaactual varchar(20)
DECLARE @contaactual varchar(20)
SET @RunningTotal = 0
SET @subcontaactual = ''
SET @contaactual = ''
--hold our results data
DECLARE @Results TABLE
(
conta varchar(20),
docnome varchar(40),
u_subconta varchar(20),
data datetime,
mlstamp varchar(25),
dinome varchar(20),
adoc varchar(20),
dilno varchar(20),
edeb numeric(10,2),
ecre numeric(10,2),
NomeCli varchar(60),
RunningTotal numeric(10,2)
)
FETCH NEXT FROM RunningTotalCursor
INTO @conta,@docnome, @u_subconta, @data, @mlstamp,@dinome, @adoc, @dilno, @edeb, @ecre, @NomeCli--, @RunningTotal
WHILE @@FETCH_STATUS = 0
BEGIN
--If there is a credit amount use it; otherwise, use debit
if @u_subconta <> @subcontaactual or @conta<>@contaactual
BEGIN
SET @RunningTotal = 0
SET @subcontaactual = @u_subconta
SET @contaactual = @conta
END
--IF @ecre > 0
--SET @RunningTotal = @RunningTotal -@ecre
--ELSE
SET @RunningTotal = @RunningTotal + (@edeb-@ecre)
--end
--insert values into the results table
INSERT @Results
VALUES (@conta,@docnome, @u_subconta, @data, @mlstamp, @dinome, @adoc, @dilno, @edeb, @ecre, @Nomecli, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @conta,@docnome, @u_subconta, @data, @mlstamp,@dinome, @adoc, @dilno, @edeb, @ecre, @Nomecli--, @RunningTotal
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
--select all records from the results table variable
SELECT Conta as 'Conta', docnome as 'Documento',u_subconta as 'NrTerceiro',data as 'Data',dinome as 'Diario',adoc as 'NrDoc',
dilno as 'NrDiario', edeb as 'Debito', ecre as 'Credito' , NomeCli as 'Cliente', RunningTotal as 'Saldo'
FROM @Results
ORDER BY conta,u_subconta
Many thanks
Luis Santos
April 28, 2011 at 12:33 pm
Are any of the values you're using to calculate the @RunningTotal NULL?
April 28, 2011 at 12:34 pm
Did you deliberately comment out @RunningTotal in your FETCH...INTO?
FETCH NEXT FROM RunningTotalCursor
INTO @conta,@docnome, @u_subconta, @data, @mlstamp,@dinome, @adoc, @dilno, @edeb, @ecre, @NomeCli--, @RunningTotal
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2011 at 4:12 pm
Hello comunity
Yes, i comment deliberately out @RunningTotal in your FETCH...INTO, because the TSQL returning error
it´s also true that is EDEB > Zero then ECRED = 0 .
But, thats ok i´am discover where is the problem, it´s because after @sql command i didn´t comment :
SELECT * FROM TMPCLICCO
like you see above.
Many thanks and best regards
Luis Santos
SET @SQL = N'
SELECT conta, docnome,u_subconta, data, mlstamp,
dinome, adoc, dilno, edeb, ecre,
(select nome from '+ @unidF + '..cl CL (nolock) where cl.no = CAST(ml.u_subconta AS INT)) NomeCli
INTO TMPCLICCO
FROM dbhaste..ml ML where ((ml.conta not like ''243%'' and ml.conta not like ''22%'')
and (ml.conta between ''' + @containi + ''' and ''' + @contafim + '''))
and month(ml.data) >= ' + convert(varchar,@mesini) + ' and month(ml.data) <= ' + convert(varchar,@mesfim) + '
and u_subconta like substring( '''+ convert(varchar,@Subconta)+ ''',1,5)
and month(ml.data) >= 0
and year(ml.data) = ' + convert(varchar,@ano) + '
AND RIGHT(RTRIM(ML.INTID),2) = ' + CONVERT(VARCHAR,@CodUnidF ) + '
--ADD WHERE AND ORDER BY CLAUSE HERE IF NEEDED
group by conta,docnome,u_subconta,data,
dinome, adoc, dilno, edeb, ecre ,mlstamp
ORDER BY conta, u_subconta,data asc ' --« Fim de N
EXEC sp_executesql @sql
--print @sql
SELECT * FROM TMPCLICCO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply