Running Totals (Cumulative Totals)
On several occassions we need to show running totals (cumulative totals) of a value in a browse. It is normally convenient to maintain the running totals in an array and display in the browse. But this involves additional work of building an array and maching it with normal data in the browse. There are times when it is more convenient, if we can directly read the data along with running totals and display directly.
In such cases, we may use SQL query like this:
MYSQL
Code: Select all | Expand
SELECT fields, amount, ( @ntotal = @ntotal + amount ) AS running_total
FROM mytable, ( SELECT @ntotal := 0 ) AS t
WHERE <clauses>
ORDER BY <clauses>
ORACLE
Code: Select all | Expand
SELECT docdt, amount, sum(amount) over (order by docdt) as running_total
FROM <mytable>
DBF (read into array)
Code: Select all | Expand
nTotal := 0
aData := FW_DbfToArray("AMOUNT,(nTotal := nTotal + AMOUNT)" )
The program posted in
viewtopic.php?f=6&t=33905can use an sql statement like this.
Code: Select all | Expand
SELECT id, ncli, fecha, descripcion, numero, tipo, importe,
( @bal := IF( tipo = '1', @bal + importe, @bal - importe ) ) AS nsaldo
FROM ctacte,
( SELECT @bal := 0 ) AS t
WHERE ncli = <client>
ORDER BY fecha
This is working sample:
maria12.prgCode: Select all | Expand
#include "fivewin.ch"
function Main()
local oCn := FW_DemoDB()
local oRs, cSql
local oDlg, oFont, oBrw
oCn:lShowErrors := .t.
TEXT INTO cSql
SELECT id, ncli, fecha, descripcion, numero, tipo, importe,
( @bal := IF( tipo = '1', @bal + importe, @bal - importe ) ) AS nsaldo
FROM ctacte,
( SELECT @bal := 0 ) AS t
WHERE ncli = ?
ORDER BY fecha
ENDTEXT
oRs := oCn:RowSet( cSql, { 101 } )
oRs:GoBottom()
DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
DEFINE DIALOG oDlg SIZE 800,700 PIXEL FONT oFont ;
TITLE "Running Totals"
@ 50,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
DATASOURCE oRs ;
COLUMNS "id", "Fecha", "Descripcion", "Numero", ;
"If( tipo == '1', importe, 0 )", ;
"If( tipo == '1', 0, importe )", ;
"nsaldo" ;
HEADERS "DocID", nil, nil, nil, "DEBE", "PAGO", "SALDO" ;
PICTURES "999", nil, nil, nil, "@EZ 999,999,999.99", "@EZ 999,999,999.99", "@EZ 999,999,999.99" ;
COLSIZES 50,100,100,100,100,100,110 ;
CELL LINES NOBORDER FOOTERS FASTEDIT
WITH OBJECT oBrw
AEval( :aCols, { |o| o:nEditType := EDIT_GET }, 3, 4 )
WITH OBJECT :Debe
:nFooterType := AGGR_SUM
:bEditValid := { |oGet| oGet:VarGet() > 0 }
:bOnPostEdit := { |o,x,n| If( o == VK_ESCAPE .or. x < 0, nil, ( oRs:tipo := '1', oRs:importe := x ) ) }
:bOnChange := { || oRs:Requery(), oBrw:Refresh() }
END
WITH OBJECT :Pago
:nFooterType := AGGR_SUM
:bEditValid := { |oGet| oGet:VarGet() > 0 }
:bOnPostEdit := { |o,x,n| If( o == VK_ESCAPE .or. x < 0, nil, ( oRs:tipo := '0', oRs:importe := x ) ) }
:bOnChange := { || oRs:Requery(), oBrw:Refresh() }
END
:Saldo:bFooter := { || oBrw:Debe:nTotal - oBrw:Pago:nTotal }
:MakeTotals()
:CreateFromCode()
END
@ 20,20 BTNBMP PROMPT "Delete" SIZE 60,20 PIXEL FLAT OF oDlg ACTION ;
( oRs:Delete(), oRs:ReQuery(), oBrw:MakeTotals(), oBrw:Refresh(), oBrw:SetFocus() )
ACTIVATE DIALOG oDlg CENTERED
oCn:Close()
return nil
Download Link:
http://anserkk.com/gnraomysql/view.php?id=13
