Show posts
Both routines are compared in this posting.
Bern,
Nice, learned a lot!!
Bern routine results:
1 payment record must exist to return results for a client
2 credit balances do not display (they weren't asked for)
3 optional, modified to add payments (passed as negative amounts)
The CJ routine could eliminate the creating temp table by reading
through the recordset and handling the debit/credit as they occur.
Made it easier by just creating a temp file with the balances.
Also, for some reason when I dropped the what I thought was unneeded third column in the
union statement t1,t2,t3 the union was not always correct. Need to look into that.
Leaving "as is" is suggested.
In this example CJ will return 4-records and Bern will return 2-records.
Fredrick, these differences should be considered.
Bern,
Nice, learned a lot!!
Bern routine results:
1 payment record must exist to return results for a client
2 credit balances do not display (they weren't asked for)
3 optional, modified to add payments (passed as negative amounts)
The CJ routine could eliminate the creating temp table by reading
through the recordset and handling the debit/credit as they occur.
Made it easier by just creating a temp file with the balances.
Also, for some reason when I dropped the what I thought was unneeded third column in the
union statement t1,t2,t3 the union was not always correct. Need to look into that.
Leaving "as is" is suggested.
In this example CJ will return 4-records and Bern will return 2-records.
Fredrick, these differences should be considered.
#INCLUDE "sqlitening.inc"
FUNCTION showit(sql AS STRING) AS STRING
LOCAL sArray() AS STRING
slselary sql,sArray(),"Q9"
FUNCTION = JOIN$(sArray(),$CR) + $CR
END FUNCTION
FUNCTION PBMAIN AS LONG
bern
cj
END FUNCTION
SUB InsertData
slexe "insert into T1 values(1,10),(1,20),(2,10),(4,100)"
slexe "insert into T2 values(1,30),(1,40),(2,10)"
slexe "insert into T3 values(1,-200),(2,0),(3,-1)"
END SUB
FUNCTION Bern AS LONG
LOCAL s AS STRING
KILL "temp.db3":IF ERR THEN ? STR$(ERRCLEAR)
slopen "temp.db3","C"
slexe "create table if not exists T1 (surname1 int,Amount1 int)"
slexe "create table if not exists T2 (surname2 int,Amount2 int)"
slexe "create table if not exists T3 (surname3 int,Amount3 int)"
InsertData
'? ShowIt("select * from t1") + $CR + ShowIt("select * from t2") + $CR + ShowIt("select * from t3")
s= "WITH"
s+=" TempT1(TT1Surname,TT1Total) AS (SELECT Surname1, Amount1"
s+=" FROM T1),"
s+=" TempT2(TT2Surname,TT2Total) AS (SELECT Surname2, Amount2"
s+=" FROM T2),"
s+=" SumT1( ST1Surname, ST1Total) AS (SELECT TT1Surname, TOTAL( TT1Total)"
s+=" FROM TempT1 GROUP BY TT1Surname),"
s+=" SumT2( ST2Surname, ST2Total) AS (SELECT TT2Surname, TOTAL( TT2Total)"
s+=" FROM TempT2 GROUP BY TT2Surname),"
s+=" SumT3( ST3Surname, ST3Total) AS (SELECT Surname3, TOTAL( Amount3)"
s+=" FROM T3 GROUP BY Surname3)"
s+=" SELECT ST3Surname as acct, ST1Total + ST2Total + ST3Total as amt"
s+=" FROM SumT3"
s+=" INNER JOIN SumT1 ON ST3Surname = ST1Surname"
s+=" INNER JOIN SumT2 ON ST3Surname = ST2Surname"
s+=" WHERE ST1Total + ST2Total <> ST3Total" 'made <> instead of subtract
'? s,,"Sql"
? ShowIt(s)
'DIM sArray() AS STRING
'slselAry s,sArray(),"Q9"
'? JOIN$(sArray(),$CR),,FUNCNAME$
slClose
END FUNCTION
FUNCTION CJ () AS LONG
LOCAL sData(),sBind AS STRING, x AS LONG
KILL "temp.db3"
slopen "temp.db3","C"
slexe "create table if not exists t1 (surname1 int,amt int)" 'charges table1
slexe "create table if not exists t2 (surname2 int,amt int)" 'charges table2
slexe "create table if not exists t3 (surname3 int,amt int)" 'payments
slexe "drop table if exists TempBalances
slexe "create table TempBalances(acct unique,amt)"
InsertData
'combine tables and payments
slselary " select surname1,sum(amt),'t1' from t1 group by surname1" +_ 'charges table1
" union " +_
" select surname2,sum(amt),'t2' from t2 group by surname2" +_ 'charges table2
" union " +_
" select surname3,sum(amt),'t3' from t3 group by surname3",sData() 'payments table3
'update TempBalances() from above sData()
FOR x = 1 TO UBOUND(sdata,2)
sBind = slBuildBindDat(sdata(1,x),"T") + slBuildBindDat(sData(2,x),"T")
slexebind "Insert into TempBalances values(?,?) ON conflict(acct) DO update SET amt=amt+" + sData(2,x),sBind
NEXT
? showit("select * from TempBalances"),,FUNCNAME$
slclose
END FUNCTION
