Solved

vb-sql programming

Posted on 2003-11-18
8
227 Views
Last Modified: 2010-04-17
i have an SQL table and want to have a Trigger for a running balance (Balance) as
Currency:       Debit:  Credit: Balance:
ZAR             10                              10
ZAR                             5               5

i have tried though not a trigger but not giving me what i want
CREATE TABLE Transactions(
   Transaction_Number NUMERIC,
   Transaction_Time TIMESTAMP,
   Account_Number INTEGER,
   Currency CHAR(8),
   Credit MONEY,
   Debit MONEY,
   PRIMARY KEY (Transaction_Number)
)
as
SELECT
   A.Transaction_Number,
   A.Transaction_Time,
   A.currency,
   A.debit,
   A.credit,
   SUM(B.Debit) - SUM(B.Credit) Balance
FROM Transactions A
JOIN Transactions B
   ON B.Account_Number = A.Account_Number
   AND B.Currency = A.Currency
   AND B.Transaction_Time <= A.Transaction_Time

GROUP BY
   A.Transaction_Number,
   A.Transaction_Time,
   A.currency,
   A.debit,
   A.credit,

ORDER BY
   A.Transaction_Time,
   A.Transaction_Number


0
Comment
Question by:gari55zw
  • 2
  • 2
8 Comments
 
LVL 9

Expert Comment

by:bhagyesht
ID: 9770258
do you want the balance only? ie the final entry of each account? Please elorate the question
0
 

Author Comment

by:gari55zw
ID: 9770806
yes, i want the final balance after a transaction. the previous transacted balance affected by transaction type as follows,

Currency:       Debit:  Credit: Balance:
ZAR             10                              10
ZAR                             5                5
ZAR              3                               8

how do i get the balance
0
 

Author Comment

by:gari55zw
ID: 9770814
yes, i want the final balance after a transaction. the previous transacted balance affected by transaction type as follows,

Currency:       Debit:  Credit: Balance:
ZAR             10                              10
ZAR                             5                5
ZAR              3                               8

how do i get the balance
0
 
LVL 9

Expert Comment

by:bhagyesht
ID: 9776328
Currency:       Debit:  Credit: Balance:
is ur input table or output format
0
 

Accepted Solution

by:
deepakshi earned 375 total points
ID: 9776955
Write the code of trigger as

CREATE TABLE Transactions(
   Transaction_Number NUMERIC,
   Transaction_Time TIMESTAMP,
   Account_Number INTEGER,
   Currency CHAR(8),
   Credit MONEY,
   Debit MONEY,
   PRIMARY KEY (Transaction_Number)
)
as
SELECT
   A.Transaction_Number,
   A.Transaction_Time,
   A.currency,
   A.debit,
   A.credit,
   SUM(B.Debit) - SUM(B.Credit)
FROM Transactions A
JOIN Transactions B
   ON B.Account_Number = A.Account_Number
   AND B.Currency = A.Currency

GROUP BY
   A.Transaction_Number,
   A.Transaction_Time,
   A.currency,
   A.debit,
   A.credit,

ORDER BY
   A.Transaction_Time,
   A.Transaction_Number



and run this trigger after u have saved the new transaction in the table.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sumDigits  challenge 7 74
create an incrementing variable name AutoHotKey 5 75
How  do I get an older program to run in Windows 10? 20 102
Device same like our heart 12 73
Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
A short article about problems I had with the new location API and permissions in Marshmallow
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now