August 20, 2006 at 11:44 pm
My scenario: I have two table one for transactions and other for balance
Suppose tables design: tblTrans(AutoID(PK) ,AccountNo,ValueDate,DrAmt,CrAmt)
TblBalance(AccountNo(PK),ValueDate(PK),Blance)
Value date can be in order or any date,
When one transaction occurred a enrty for tblTrans table and also update/insert in tblBlance Table.
Suppose
ValueDate AccountNo DrAmt CrAmt
Trans1 12/08/06 ‘123’ 120 0
Trans2 20/08/06 ‘123’ 0 500
Trans3 13/08/06 ‘123’ 0 400
Trans4 13/08/06 ‘123’ 100 0
Trans5 14/08/06 ‘123’ 0 100
TblBalance table entry
AccountNo ValueDate Balnce
After Trans1 ‘123’ 12/08/06 -120
After Trans2 ‘123’ 20/08/06 380+400-100+100
After Trans3 ‘123’ 13/08/06 -280
After Trans4 ‘123’ 13/08/06 -280-100
After Trans5 ‘123’ 14/08/06 -280-100+100
This is a multi user system I could not full lock the table becoz other account entry possible in same time but more than one user entry for same account who can I control Concurreny?
August 22, 2006 at 12:15 pm
My initial question would be why do you need TblBalance? When you need to show the balance in your application why not calculate it on the fly? SELECT SUM(dramt) - Sum(CrAmt) AS Balance FROM tblTrans WHERE AccountNo = '123'
And if you need a balance for a specific date specify the date range in your where clause as well.
If you really need it stored in TblBalance, do you need it stored as '-280-100' or would -380 work for you? If -380 will work, you could use an insert trigger on TblTrans. Use and Update, Insert and probably delete as well just to cover your bases and have the trigger insert the record into tblBalance so that it happens as soon as the record is inserted into TblTrans. something like
CREATE TRIGGER UIT_UpdateTblBalance ON tblTrans
FOR AFTER INSERT
AS
INSERT INTO TblBalance
SELECT T.AccountNo, GETDATE() AS ValueDate, (SUM(T.dramt) - SUM(T.CrAmt)) AS Balance
FROM tblTrans T INNER JOIN Inserted I ON T.AccountNo = I.AccountNo GROUP BY T.AccountNo
August 22, 2006 at 10:27 pm
first thing: I need balance table
i cannot explain my problem cleary:
my code is look like
mybalance table suppose tblBalance(AccountNo(PK),ValueDate(pK),Balance)
in this table valueDate can be any date in any order.
and my code suppose:
'------------------------------------
Private cnn As New ADODB.Connection
Private Sub cmdSave_Click()
Dim cm As New ADODB.Command
Dim rs As New ADODB.Recordset
On Error GoTo ERR1
cnn.BeginTrans
cm.ActiveConnection = cnn
cm.CommandType = adCmdText
cm.CommandText = "select ValueDate,Balance from tblBalance where AccountNo='" & txtAcNo.Text & "'" & _
" and ValueDate=(select max(ValueDate) from tblBalance where AccountNo='" & txtAcNo.Text & "' and ValueDate<='" & txtValueDate.FormattedText & "')"
Set rs = cm.Execute
If rs.RecordCount = 0 Then
cm.CommandText = "insert into tblBalance(AccountNo,Valuedate,Balance) values(" & _
"'" & txtAcNo.Text & "','" & txtValueDate.FormattedText & "'," & Val(txtAmount.Text) & ")"
cm.Execute
cm.CommandText = "Update tblBalance set Balance=Balance+" & Val(txtAmount.Text) & _
" where AccountNo='" & txtAcNo.Text & "' and ValueDate>'" & txtValueDate.FormattedText & "'"
cm.Execute
ElseIf rs.Fields("ValueDate") = CDate(txtValueDate.FormattedText) Then
cm.CommandText = "Update tblBalance set Balance=Balance+" & Val(txtAmount.Text) & _
" where AccountNo='" & txtAcNo.Text & "' and ValueDate>='" & txtValueDate.FormattedText & "'"
cm.Execute
Else
cm.CommandText = "Update tblBalance set Balance=Balance+" & Val(txtAmount.Text) & _
" where AccountNo='" & txtAcNo.Text & "' and ValueDate>'" & txtValueDate.FormattedText & "'"
cm.Execute
cm.CommandText = "insert into tblBalance(AccountNo,Valuedate,Balance) values(" & _
"'" & txtAcNo.Text & "','" & txtValueDate.FormattedText & "'," & Val(txtAmount.Text) + rs.Fields("Balance") & ")"
cm.Execute
End If
cnn.CommitTrans
Exit Sub
ERR1:
cnn.RollbackTrans
MsgBox Err.Description
End Sub
'--------------------------------------
if you enrty
1st: ac=123 valuedate='12/08/06' amount=1200
tblBalance table:
acNo Valuedate balance
123 12/08/06 1200
2nd: ac=123 valuedate='20/08/06' amount=2000
tblBalance table:
acNo Valuedate balance
123 12/08/06 1200
123 20/08/06 2000+1200=3200
3rd: ac=123 valuedate='14/08/06' amount=1400
tblBalance table:
acNo Valuedate balance
123 12/08/06 1200
123 20/08/06 3200+1400=4600
123 14/08/06 1200+1400=2600
4th: ac=123 valuedate='14/08/06' amount=300
tblBalance table:
acNo Valuedate balance
123 12/08/06 1200
123 20/08/06 4600+300=4900
123 14/08/06 2600 +300=2900
for concurreny problem:
it should be possible by using transaction isolation lavel? if yes then
what Transaction Isolation level should I use and why?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply