I need help with a sum or substract on a bank account 4

[Gert]

Hi all, what i need is to make a sum to get a balance on a bank accound database.

I have this on my table:

numid accid type amount
1 1000 D 1200
2 1000 D 1000
3 1000 C 500
4 1000 C 200
5 1000 D 1000
6 1000 C 500
7 1000 C 300
8 1000 D 2000

what i need is another table with the balance of the account
example:
numid accid type amount balance
1 1000 D 1200 1200
2 1000 D 1000 2200
3 1000 C 500 1700
4 1000 C 200 1500
5 1000 D 1000 2500
6 1000 C 500 2000
7 1000 C 300 1700
8 1000 D 2000 3700

C= Cheq and most be sustract
D= Depot and mos be added

some help will be apreciated
thanks in advance

numid accid type amount balance

 

[rgbean]

This is one of those times when an SQL solution is very difficult, but an xBase one is pretty trivial.
Assuming the numid is increasing based on the datetime and you can index the file on accid+numid [if they are character fields - if they are numeric then str(accid,len(accid))+str(numid,len(numid))], then:

LOCATE && go top
lcSvAccID = "0000000000" && non-existent number
SCAN
   IF lcSvAccID <> accid
      lnBalance = 0
      lcSvAccID = accid
   ENDIF
   DO CASE
   CASE TranType = &quot;D&quot;
      lnBalance = lnBalance + Amount
   CASE TranType = &quot;C&quot;
      lnBalance = lnBalance - Amount
   OTHERWISE
      * ??
   ENDCASE
   REPLACE Balance with lnBalance
ENDSCAN

Rick

 

[AirCon]

Gert,

You can create a function (UDF) with select statement. Run this code:

*----------------------

Private pnBalance, plStart

Create cursor Table1 ;
(NumID N(3), AccID N(5), Type C(1), Amount N(6))
Insert into Table1 values (1, 1000, 'D', 1200)
Insert into Table1 values (2, 1000, 'D', 1000)
Insert into Table1 values (3, 1000, 'C', 500)
Insert into Table1 values (4, 1000, 'C', 200)
Insert into Table1 values (5, 1000, 'D', 1000)
Insert into Table1 values (6, 1000, 'C', 500)
Insert into Table1 values (7, 1000, 'C', 300)
Insert into Table1 values (8, 1000, 'D', 2000)

pnBalance = 0 && Initiliaze
plStart = .T. && Start SQL
Select *, SumAmount() as Balance from Table1 into cursor Table2
Browse nowait


Function SumAmount
If plStart
plStart = .F.
else
If (Type == 'D')
pnBalance = pnBalance + Amount
else
pnBalance = pnBalance - Amount
endif
endif

Return pnBalance
EndFunc

*----------------------


-- AirCon --

 

[ramani]

Hi

SELECT the records using SQL.. and immediately following that.. use Ricks code. That is the way to go.

Aircons code will not work correctly all the time, since the order in which the records are extracted will disturb the functions output.. SQL will get the records and so get the balances updated by the function.. and then when ordering the records.. the rows position will get changed. The result will look akward. (Aircon.. no hard feelings. We all slip at times).




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Gert]

thanks to all

 

[craigsboyd]

Gert,

If you want a pure SQL solution I would go with this:

Create cursor Table1 ;
(NumID N(3), AccID N(5), Type C(1), Amount N(6))
Insert into Table1 values (1, 1000, 'D', 1200)
Insert into Table1 values (2, 1000, 'D', 1000)
Insert into Table1 values (3, 1000, 'C', 500)
Insert into Table1 values (4, 1000, 'C', 200)
Insert into Table1 values (5, 1000, 'D', 1000)
Insert into Table1 values (6, 1000, 'C', 500)
Insert into Table1 values (7, 1000, 'C', 300)
Insert into Table1 values (8, 1000, 'D', 2000)

SELECT b.NumID, b.AccID, b.Type, b.amount, ;
sum(IIF(a.Type = "C", a.Amount * -1, a.Amount)) as balance ;
FROM Table1 a right JOIN Table1 b ;
ON b.NumID => a.NumID ;
GROUP BY b.NumID


Slighthaze = NULL
[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[craigsboyd]

A couple of things to note on the above query...

...the join doesn't really matter, it works with Inner, Left, or Right, the key to it is joining the table to itself (since we have to do everything in a single pass we can simulate multiple passes by joining a table to itself), the join is on b.NumID => a.NumID which means that each record will receive the sum of its twin (for lack of a better word) record and all the records before it, but nothing above it (by NumID). Anyways, I think it works good for what you are trying to do here.

Slighthaze = NULL
[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[rgbean]

Craig,
While there is very little difference in timing for a small amount of data, I have a feeling that the "pure" SQL solution is going to be very slow compared to the xBase technique when you start dealing with "real world" data. If you just think about doing a subquery for each record, by the time you get to the last record, you've done an awful lot of IO (even if it does some caching) rather than the one pass through the data in xBase!

Rick

 

[AirCon]

Ramani,

No offense taken, We are all learn here :-D
Just one thing. I can't find the condition where my code above failed. Can you explain a bit more detail how it failed? with some code perhaps ?

-- AirCon --

 

[craigsboyd]

Rick,

You are absolutely right, I wasn't really thinking of it from that perspective, just trying to come up with a pure SQL way of doing this. I guess the technique has perhaps some merrit in certain situations or as a guide on what NOT to do if you want performance. I decided to see how bad the performance was...it sure doesn't take long for it to degradate. Up until 0-500 records everything ran ok (the scan code was always faster) 501-1000 records it was bareable, 1001=10000 it still worked if you could go get a cup of coffee and you were running it on a very fast computer (3.06ghz with 512ram). 10001-1000000 recs i ended up terminating the query it took so long.

Moral of the story is this, if you want to keep a running balance on your child's piggy bank then use that SQL I wrote, but if you actually want to put this into a production system use Rick's code.

Slighthaze = NULL
[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[rgbean]

Craig,
I hope you know it wasn't my intention to 'embarass' you or anyone else. It's just that I've occasionally written "cool" code that works fine in testing, only to have it out right fail in production. I always try to think about the underlying implications or my code and always try to adhere to the K.I.S.S. philosophy - "Keep It Simple Stupid"!

Rick

 

[craigsboyd]

Rick,

I absolutely know that you weren't trying to embarass me. I never took it that way at all and only took the opportunity to poke fun at myself and my code. I'm not sure how to exactly word my thoughts...I have worked with far too many programmers and management that lived by the adage "It works don't it?". I mean I am my chief critic and I want to be surrounded by people who set high standards, especially when it comes to programming/development/design. I hate it when people candy coat stuff, and would just as soon have people speak there minds. if something is bad or there are concerns about it then I prefer it if they say "This is bad and this is why it's bad.". If I have a defense or don't agree then I will voice it and give my reasons. If not, then I will have learned something and be better equipped the next time around. Either way it's a win-win to me, but not all the world sees it that way. With some feelings get involved because in some ways the work is a reflection on the worker. As for me I analyze and re-analyze things to the point of becoming anal-retentive. So to you and anyone else I say "FIRE AWAY!", I have broad shoulders and can take the heat.

I am interested to see what ramani's answer is to Aircon. Though I haven't spent a great deal of time looking it over, I must say that I can't readily see the problem that ramani is referring to regarding the order changing and causing Aircon's solution to present "awkward" results.

Slighthaze = NULL
[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[ramani]

Hi

Sorry for the delay... These days I am not appearing here enough.

To answer Aircon and Slighthaze...

The reason I suggested is almost very similar to what Rick said. We will work on test lines and the production line could turn that out to be more complicated than expected. The date and certain sequences could be important in a bank statement.

in the Aircon's example..
If it were to be ordered.... you can see the result is not as desired. We cannot assume that the requirement is the natural order and that will be the final.

*----------------------
Private pnBalance, plStart

Create cursor Table1 ;
(NumID N(3), AccID N(5), Type C(1), Amount N(6))
Insert into Table1 values (1, 1000, 'D', 1200)
Insert into Table1 values (2, 1000, 'D', 1000)
Insert into Table1 values (3, 1000, 'C', 500)
Insert into Table1 values (4, 1000, 'C', 200)
Insert into Table1 values (5, 1000, 'D', 1000)
Insert into Table1 values (6, 1000, 'C', 500)
Insert into Table1 values (7, 1000, 'C', 300)
Insert into Table1 values (8, 1000, 'D', 2000)

pnBalance = 0 && Initiliaze
plStart = .T. && Start SQL
Select *, SumAmount() as Balance from Table1 into ;
cursor Table2 ORDER BY 3
Browse nowait


Function SumAmount
If plStart
plStart = .F.
else
If (Type == 'D')
pnBalance = pnBalance + Amount
else
pnBalance = pnBalance - Amount
endif
endif

Return pnBalance
EndFunc
*----------------------



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[thuvan1957]

Where are GERT ???
the program of Ramani is very good !!!

 

[AirCon]

Ramani,

I get your point. I thought what you meant is using "SET ORDER TO...". You were right, the result looks awkward because the calculation is done at the first passed.

So I'm going to change the code :-D.
As suggested (Select then Replace) to make it work with/without ORDER clause


*---------------

Private pnBalance

Create cursor Table1 ;
(NumID N(3), AccID N(5), Type C(1), Amount N(6))
Insert into Table1 values (1, 1000, 'D', 1200)
Insert into Table1 values (2, 1000, 'D', 1000)
Insert into Table1 values (3, 1000, 'C', 500)
Insert into Table1 values (4, 1000, 'C', 200)
Insert into Table1 values (5, 1000, 'D', 1000)
Insert into Table1 values (6, 1000, 'C', 500)
Insert into Table1 values (7, 1000, 'C', 300)
Insert into Table1 values (8, 1000, 'D', 2000)

pnBalance = 0
Select *, Amount as Balance from Table1 ;
into cursor Table2 order by Type readwrite
Replace all Balance with SumAmount()
Go top
Browse nowait


Function SumAmount
If (Type == 'D')
pnBalance = pnBalance + Amount
else
pnBalance = pnBalance - Amount
endif

Return pnBalance
EndFunc

*---------------


-- AirCon --

 

[Gert]

rgbean option work excelent for me,

thanks a lot to all