Wow! Thanks Albert and Bill, you guys are awesome, im gonna try these out.
((((((: <---big smile ----- Original Message ----- From: "Bill Downall" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, July 30, 2003 3:41 PM Subject: [RBASE-L] - Re: SQL longshot! > Atrix, > > I think this might help get you there. > > CREATE VIEW AtrixTranSummary + > (AcctID, OrigApp, BalanceChange, NewBalance) + > AS SELECT + > t2.AcctID, + > a1.OrigApp, > SUM (IFEQ(TranState,'D', (AcctIDAmount), 0) - + > IFEQ(TranState,'A', (AcctIDAmount), 0) - + > IFEQ(TranState,'B', (AcctIDAmount), 0)), + > SUM (OrigApp + IFEQ(TranState,'D', (AcctIDAmount), 0) - + > IFEQ(TranState,'A', (AcctIDAmount), 0) - + > IFEQ(TranState,'B', (AcctIDAmount), 0)), + > FROM Accounts a1, TranAcctID t2 + > WHERE TranState <> 'C' + > AND (a1.AcctID = t2.AcctID) + > GROUP BY t2.AcctID > > Bill > > On Wed, 30 Jul 2003 15:25:38 -0700, Atrix Wolfe wrote: > > >hey guys, > > > >I have a problem im working on and was wondering if theres a way to do > >it more efficient than i am > > > >I have 2 tables, One stores account numbers, their unique ID and their > >starting balance > > > >Table Accounts: > >Accountno text 50 > >Acctid integer > >OrigApp currency > > > >The other stores acctids, an amount for a transaction and a transaction > >type of A,B,C or D. > > > >Table TranAcctid: > >Acctid integer > >AcctidAmount currency > >TranState text 1 > > > >The balance of an account = OrigApp - TranAcctid(A's) - TranAcctid(B's) > >+ TranAcctid(D's) > > > >so A and B are subtracted, C is ignored and D is added... > > > >The way im doing it now is joining the accounts table with tranacctid > >and manualy looping through the results with witango and calculating the > >balances that way...then after that i have to go back to the database to > >get all the accounts that dont use transactions (arent in tranacctid) > >and display those. > > > >kinda a pain and pretty darn slow! > > > >is there a way to get the balance of all the accounts with a single sql > >statement? Some kind of crazy join and group by or something? > > > >Thanks for the help! > >Atrix >
