October 16, 2007 at 4:46 pm
Hello comunity
I have a TSQL to return an account of my customer, like this
date document debit credit balance
16-10-2007 inv.111 100,00 100,00
17-10-2007 inv.112 20,00 20,00 <--- not correct , the correct value is 80,00
I need to place on my column balance the correct balance value that is neither 100,00 or 20,00 but 80,00. the value is the sum of debit column - sum of credit column, all for each line.
I don´t know if it is possible on TSQL statment.
Also , if i demand for all my customers, when the customer change the column balance is put to 0,00 and they make new calcluation for the other customer.
Many thanks
Luis Santos
October 17, 2007 at 3:13 am
Search the forum you will surely find many articles on it...
--Ramesh
October 17, 2007 at 4:01 am
Hello Ramesh
Thanks for your reply, but to search on site do you have an ideia.....!
Also, if anyone give me the solution, i thinks it´s difficult an existing Post regarding my problem
Thanks again
Luis Santos
October 17, 2007 at 4:22 am
Search for "running total" or "running balance" on these forums.
Basically to do this in SQL and calculating this in queries is very inefficient and does not scale. You are much better at doing this on the client side. Using triggers can help, but it also comes with a price tag.
Regards,
Andras
October 17, 2007 at 8:18 am
luissantos,
you can use a statement like below.
declare @MyTable table
(
RowID int IDENTITY,
dt varchar(10),
doc varchar(20),
DEBamt smallmoney,
CREamt smallmoney
)
--date | document | debit | credit | balance
insert into @MyTable values ('16-10-2007', 'inv.111',0,100.00)
insert into @MyTable values ('17-10-2007', 'inv.112',20.00,0)
insert into @MyTable values ('18-10-2007', 'inv.113',10.00,0)
insert into @MyTable values ('19-10-2007', 'inv.114',0,25.99)
SELECT
min(a.dt) as [Date],
min(a.doc) as [Document],
min(a.DEBamt) as [Debit Amount],
min(a.CREamt) as [Credit Amount],
sum(b.CREamt-b.DEBamt) AS [RunningTotal]
FROM
@MyTable as a
inner join
@MyTable as b
on b.dt <= a.dt
and a.RowID > 0
and b.RowID > 0
group by
a.RowID
ORDER BY
min(a.dt)
October 17, 2007 at 11:12 am
Hello Adam
Thanks for your reply, in fact is exactly what i need.
But you use a Temp table, can i use your example with the following TSQL script.Could you give me some help on this code :
select 0 as dilno,0 as dino,'' as dinome,'' as docnome,'' as adoc,conta,
u_subconta,(case when ml.conta like '21%' and u_subconta not like 'B%' then (select nome from cl (nolock) where cl.no=cast(ml.u_subconta as int)) else
case when ml.conta in ('22%','25%','26%') and ml.u_subconta not like 'B%' then (select nome from fl (nolock) where cast(ml.u_subconta as int)=fl.no) else case when ml.conta like '21%' and u_subconta like 'B%' then (select conta from bl (nolock) where bl.banco=ml.u_subconta)
else case when ml.conta in ('22%','25%','26%') and ml.u_subconta like 'B%' then (select conta from bl (nolock) where bl.banco=ml.u_subconta) else ' ' end end end end) as nome,
'Acumulados' as descritivo,sum(debit) as debit,
sum(credit) as ecre ,sum(debit-credit) as amount,0 as data,0 as mes,0 as dia,0 as ano
from ml (nolock)
where ml.conta>=#1# and ml.conta<=#2# and
month(ml.data) < month(#3#) and year(ml.data)=year(#3#)
group by
conta,u_subconta
union all
select cast(dilno as varchar(20)),
dino,
dinome,
isnull((select top 1 docnome from dc where dc.docno=ml.doctipo),space(40)) as docnome,
adoc,
conta,
u_subconta,
(case when ml.conta like '21%' and u_subconta not like 'B%' then (select nome from cl (nolock) where cl.no=cast(ml.u_subconta as int)) else
case when ml.conta in ('22%','25%','26%') and ml.u_subconta not like 'B%' then (select nome from fl (nolock) where cast(ml.u_subconta as int)=fl.no) else case when ml.conta like '21%' and u_subconta like 'B%' then (select conta from bl (nolock) where bl.banco=ml.u_subconta)
else case when ml.conta in ('22%','25%','26%') and ml.u_subconta like 'B%' then (select conta from bl (nolock) where bl.banco=ml.u_subconta) else ' ' end end end end) as nome,
descritivo,
sum(edeb) as edeb,
sum(ecre) as ecre,
sum(edeb-ecre) as Saldo,
data,
cast(mes as int),
cast(dia as varchar(2)),
year(data) as ano
from ml
(nolock)
where ml.conta>=#1# and ml.conta month(#3#) ) or (YEAR(ml.data) = year(#3#) and
ml.mes = month(#3#) and (case when ml.dia = 0 then datepart(dd, ml.data) else ml.dia end) >= day(#3#))) and (YEAR(ml.data) < year(#4#) or
(YEAR(ml.data) = year(#3#) and ml.mes < month(#3#))
or (YEAR(ml.data) = year(#3#) and ml.mes = month(#3#) and (case when ml.dia = 0 then datepart(dd, ml.data) else ml.dia end) <= day(#4#)))
group by
conta,u_subconta,data,mes,dia,dino,dinome,dilno,doctipo,adoc,descritivo
order by
conta,u_subconta,ano,mes,dia,dinome,dilno
#1# and #2# and #3# are variables on my client syde VFP application.
I hope you can understand my TSQL coding and give me some help to obtain the same result on your script.
Many thanks
Luis Santos
October 17, 2007 at 2:07 pm
luissantos,
You should be able to do something like:
min(a.Debit) as [Debit Amount],
min(a.Credit) as [Credit Amount],
sum(b.Credit-b.Debit) AS [RunningTotal]
And In the From clause add the table aliases
FROM ml as a inner join ml as b
on b.YOUR_DATE_FIELD <= a.YOUR_DATE_FIELD
Finally make sure you group by YOUR_DATE_FIELD
October 17, 2007 at 2:41 pm
Hello Adam
Many thanks again for your useful help and interest.
I will try your advise
Thank you
Luis Santos
October 17, 2007 at 4:53 pm
Hello again Adam
sorry, but i test your script and for that i simplify my TSQL like that :
SELECT min(a.conta) as [Conta], min(a.u_subconta) as [subconta], min(a.data) as [Date], min(a.dinome)as [diario],min(a.adoc) as [doc],
min(a.dilno) as [Document],
min(a.edeb) as [Debit Amount],
min(a.ecre) as [Credit Amount],
sum(b.edeb-b.ecre) AS [RunningTotal]
FROM ml as a
inner join ml as b on b.conta <= a.conta and a.mlstamp= b.mlstamp
and a.conta like '21101'
where year(a.data)=2007 and month(a.data)=1
group by a.mlstamp
ORDER BY 2,3
note : the field mlstamp is PrimaryKey
on the colum [RunningTotal] i have per line the value of [Debit amount] if >0 or [Credit Amount] if >0
but i cannot make SUM.
ContasubcontaDatediariodocDocumentDebit AmountCredit AmountRunningTotal
21101500022007-01-0110 - Abertura1000001541074,590541074,59 - OK it´s the first line
21101500022007-01-0524 - Letras a Receber2061338100028512500012500,00 - not OK correct value : 553574,59
21101500022007-01-0824 - Letras a Receber2061167100026350000050000,00 - Not OK correct value : 603574,59
21101500022007-01-0824 - Letras a Receber206097610002372938200293820,00 - not OK correct value : 897394,59
21101500022007-01-1024 - Letras a Receber8100000802254230-2254230,00 - Not OK correct value : -1356835,41
21101500022007-01-1024 - Letras a Receber20613391000287900009000,00 - Not OK correct value : -1347835,41
21101500022007-01-1231 - Vendas100001000001225423002254230,00 - Not OK correct value : 906394,59
and so on....
Like you see the RunningTotal column is not correct.
Could you help me again
Many thanks
Luis Santos
October 17, 2007 at 8:09 pm
luissantos,
Against my normal practices I have written a cursor that will perform the same calculation :crazy:. The cursor should perform better, if there are lots of rows involved. The main piece of code that drives this cursor is the select statment that appears right before the cursor is opened. You can adjust this to suit your needs. If you need a reference for running sum queries, you can visit http://sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18286.aspx. This page explains the theory and logic of the code.
Adam
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT conta, u_subconta, data,
dinome, adoc, dilno, edeb, ecre
FROM FROM ml
--ADD WHERE AND ORDER BY CLAUSE HERE IF NEEDED
ORDER BY conta, u_subconta
OPEN RunningTotalCursor
--I am not sure of the datatypes you need, so change accordingly
DECLARE @conta varchar(20)
DECLARE @u_subconta varchar(20)
DECLARE @data datetime
DECLARE @dinome varchar(20)
DECLARE @adoc varchar(20)
DECLARE @dilno varchar(20)
DECLARE @edeb smallmoney
DECLARE @ecre smallmoney
--set starting value of the running total
DECLARE @RunningTotal smallmoney
SET @RunningTotal = 0
--hold our results data
DECLARE @Results TABLE
(
conta varchar(20),
u_subconta varchar(20),
data datetime,
dinome varchar(20),
adoc varchar(20),
dilno varchar(20),
edeb smallmoney,
ecre smallmoney,
RunningTotal smallmoney
)
FETCH NEXT FROM RunningTotalCursor
INTO @conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre
WHILE @@FETCH_STATUS = 0
BEGIN
--If there is a credit amount use it; otherwise, use debit
IF @CREamt > 0
SET @RunningTotal = @RunningTotal + @ecre
ELSE
SET @RunningTotal = @RunningTotal - @edeb
--insert values into the results table
INSERT @Results
VALUES (@conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
--select all records from the results table variable
SELECT *
FROM @Results
ORDER BY conta,u_subconta
October 18, 2007 at 12:58 am
Andras Belokosztolszki (10/17/2007)
Search for "running total" or "running balance" on these forums.Basically to do this in SQL and calculating this in queries is very inefficient and does not scale. You are much better at doing this on the client side. Using triggers can help, but it also comes with a price tag.
Regards,
Andras
True in most cases... but not if you do it right 😀 And, doing it right means being able to get the running total on a million rows in 13 SECONDS...
Abdras us correct... the methods that use cursors or the <= "Triangular" joins (please see http://www.sqlservercentral.com/Forums/Topic359124-338-1.aspx#bm360151 for what "triangular" joins are and why they're so bad) are going to be slow... and the triangular join method can be thousands of times slower than a cursor. Still, we don't need to resort to the slothfullness of a cursor.
First, let's build a million row test table, just to prove the point... this is my standard test table for a lot of "performance proofs"....
[font="Courier New"]--===== Create and populate a 1,000,000 row test table.
     -- Column RowNum has a range of 1 to 1,000,000 unique numbers
     -- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
     -- Column "SomeString" has a range of "AA" to "ZZ" non-unique 2 character strings
     -- Column "SomeNumber has a range of 0.0000 to 99.9999 non-unique numbers
     -- Column "SomeDate" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times
     -- Takes about 80 seconds to execute.
 SELECT TOP 1000000
        RowNum     = IDENTITY(INT,1,1),
        SomeInt    = CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT),
        SomeString = CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
                   + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)),
        SomeCSV    = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
        SomeNumber = CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY),
        SomeDate   = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME)
   INTO dbo.JBMTest
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
  ALTER TABLE dbo.JBMTest
        ADD PRIMARY KEY CLUSTERED (RowNum)[/font]
Ok... like I said... that's the test table... it's not part of the solution... just part of the demonstration.
Now, let's say we want to do the running total or running balance on that table using the SomeNumber Column as the dollar amount to be totalled... the following is amazingly fast...
[font="Courier New"]--===== Create the temp table with just the data we need...
     -- Takes just 3 seconds
 SELECT RowNum, SomeDate, SomeNumber, RunningTotal = CAST(0 AS MONEY)
   INTO #RunningBal
   FROM dbo.jbmTest
--==== Instead of using ORDER BY, we'll create a very important index...
    -- Takes just 5 seconds
CREATE CLUSTERED INDEX IDX_tmpRunningTotal_SomeDate_SomeNumber ON #RunningBal(SomeDate,SomeNumber)
--===== Declare a local variable for the running total calculation
     -- Takes no time at all
DECLARE @RunningTotal MONEY
    SET @RunningTotal = 0
--===== Create the running total... this is so fast, it's truly amazing...
     -- Takes just 5 seconds to run.
     -- The trick with the index forces the order.  THAT'S THE KEY!!!
 UPDATE #RunningBal 
    SET @RunningTotal = RunningTotal = @RunningTotal+SomeNumber
  FROM #RunningBal WITH (INDEX(IDX_tmpRunningTotal_SomeDate_SomeNumber),TABLOCK)
--===== Finally, display the first 100 rows as a sanity check.
     -- Takes no time at all
     -- Notice the running total column?
 SELECT top 100 *
   FROM #RunningBal 
  ORDER BY SomeDate,SomeNumber[/font]
There will be some that will say it can fail if Microsoft changes the way the optimizer works... while that may be true, they haven't yet and this method hasn't failed yet, either... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2007 at 5:02 am
Hello Adam
I test your last reply , in this moment the problem exist when the field u_subconta change, in this case the RunningTotal must reset to zero, because it´s an another customer and they must recalculate the runningTotal. This situation must have a solution, i will send you my script based on your last one :
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT conta, u_subconta, data,
dinome, adoc, dilno, edeb, ecre
FROM ml where conta like '21101' and conta like '21102'
--ADD WHERE AND ORDER BY CLAUSE HERE IF NEEDED
group by conta,u_subconta,data,
dinome, adoc, dilno, edeb, ecre
ORDER BY conta, u_subconta
OPEN RunningTotalCursor
--I am not sure of the datatypes you need, so change accordingly
DECLARE @conta varchar(20)
DECLARE @u_subconta varchar(20)
DECLARE @data datetime
DECLARE @dinome varchar(20)
DECLARE @adoc varchar(20)
DECLARE @dilno varchar(20)
DECLARE @edeb numeric(10,2)
DECLARE @ecre numeric(10,2)
--set starting value of the running total
DECLARE @RunningTotal numeric(10,2)
SET @RunningTotal = 0
--hold our results data
DECLARE @Results TABLE
(
conta varchar(20),
u_subconta varchar(20),
data datetime,
dinome varchar(20),
adoc varchar(20),
dilno varchar(20),
edeb numeric(10,2),
ecre numeric(10,2),
RunningTotal numeric(10,2)
)
FETCH NEXT FROM RunningTotalCursor
INTO @conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre
WHILE @@FETCH_STATUS = 0
BEGIN
--If there is a credit amount use it; otherwise, use debit
IF @ecre > 0 and @conta=@conta
SET @RunningTotal = @RunningTotal -@ecre
ELSE
SET @RunningTotal = @RunningTotal + @edeb
--insert values into the results table
INSERT @Results
VALUES (@conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre, @RunningTotal)
FETCH NEXT FROM RunningTotalCursor
INTO @conta, @u_subconta, @data, @dinome, @adoc, @dilno, @edeb, @ecre
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
--select all records from the results table variable
SELECT *
FROM @Results
ORDER BY conta,u_subconta
I hope you have some magic solution
Many thanks
Luis Santos
October 18, 2007 at 8:15 pm
Luis,
How many rows do you end up with in your result set?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 19, 2007 at 2:23 am
Hello Jeff
I hold many lines because i want to return for each u_subconta (account of Customer) all the movment of this account.But i thinks that is not the problem.
My problem is :
For exemple:
conta is always = 21101 but u_subconta= 50002, 50004......on so on
the cursor work fine for the first customer, but on the following customers the cursor always sum(RunningTotals)
ex.
conta u_subconta Debit Credit RunningTotal in the cursor
21101 50002 100000,00 0,00 100000,00 100000,00
21101 50002 50000,00 0,00 150000,00 150000,00
21101 50002 0,00 20,00 149980,00 149980,00
21101 50010 200,00 0,00 200,00 OK 149960,00 BAD
21101 50010 50,00 150,00 OK 149810,00 BAD
Like you see the column RunningTotals must reset to Zero when Conta+U_subconta changed
I hope you can help me
Many thanks
Luis Santos
Luis Santos
October 20, 2007 at 8:43 am
luissantos (10/19/2007)
I hold many lines because i want to return for each u_subconta (account of Customer) all the movment of this account.But i thinks that is not the problem.My problem is :...
Ah... but that's why I asked... "Many lines" is a major part of the problem. Takes too long to experiment for correct answer because the cursor is slow even for "few lines". 😀
I have a couple of things to do first but I'll be back... thank you for the clarification of what you wanted to do.
By the way, the example code I gave you will solve the problem if you just tweek the CASE statement and add another variable to the mix...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply