Solved

vb-sql programming

Posted on 2003-11-18
8
231 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

691 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