gari55zw
asked on
vb-sql programming
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
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
bhagyesht
do you want the balance only? ie the final entry of each account? Please elorate the question
ASKER
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
Currency: Debit: Credit: Balance:
ZAR 10 10
ZAR 5 5
ZAR 3 8
how do i get the balance
ASKER
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
Currency: Debit: Credit: Balance:
ZAR 10 10
ZAR 5 5
ZAR 3 8
how do i get the balance
Currency: Debit: Credit: Balance:
is ur input table or output format
is ur input table or output format
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.