November 2, 2015 at 8:49 pm
Hi,
I have some transactions with the same card number that needs to add value amount to its existing balance. For example:
Card Number Balance Amount Issue Date Issue Branch.
4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.
Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130.
How can I update the card number table with debit/credit transactions to keep the outstanding balance?
Thanks,
November 2, 2015 at 10:42 pm
Quick question is there any transaction table where you are storing each Debit / Credit entry for the specific card number. If yes then share the DDL for that table with some sample data you have.
If you directly update the card Number table (as you didn't shared the name and DDL of the table) well in this case you only need to update table on each transaction made for a card number i-e
If Debit Transaction you need to do this
Update [YourTableName]
Set BalanceAmount = BalanceAmount + [DebitAmount]
Where CardNumber = YourCardNumber
If Credit Transaction you need to do this
Update [YourTableName]
Set BalanceAmount = BalanceAmount - [DebitAmount]
Where CardNumber = YourCardNumber
There SHOULD BE a transaction table otherwise you will never able to track what have changed the balance amount of the respective table.
November 3, 2015 at 5:01 am
See this paper for accounting database design. I haven't found original publication by Michael Wigley.
November 3, 2015 at 8:22 am
Yes, below is a sample of the transaction table.
Card Number Transaction Date StatusTransaction AmountRemaining BalanceYearMonthPeriod
400011112222333000009/20/2015 S 100 100 2015 9 9
4000111122223330000010/4/2015 R -50 50 2015 10 9
4000111122223330000010/20/2015 A 20 70 2015 10 10
4000111122223330000010/31/2015 R -70 0 2015 10 10
When you use UPDATE table to set the BalanceAmount by adding BalanceAmount + [DebitAmount] method, can we put this to a separate table instead of updating the transaction table?
Thanks,
November 4, 2015 at 12:48 am
Instead of updating the running total each time as you have to calculate the running total again and again and don't forget the locking on the table if the transaction are on the higher side.
As this question is under SQL SERVER 2012 forum it assumes that you are working on SQL SERVER 2012.
Following is the way you can calculate the Running Balance at any given time. Solution below uses Window Function Using Row UNBOUNDED PRECEDING feature only available to SQLSERVER2012 and above.
Declare @TransactionTable table
(
CardNumber nvarchar(20),
TransactionDate date,
[Status] Char(1),
TransactionAmount decimal(11,5),
RemainingBalance decimal(11,5),
Year smallint,
Month tinyint,
Period int
)
insert into @TransactionTable
SELECT '40001111222233300000' AS CardNumber,'9/20/2015' AS TransactionDate, 'S' AS Status, 100AS TransactionAmount, 100AS RemainingBalance, 2015 AS Year, 9 AS Month, 9AS Periodunion all
SELECT '40001111222233300000' AS CardNumber,'10/4/2015' AS TransactionDate, 'R' AS Status, -50AS TransactionAmount, 50AS RemainingBalance, 2015 AS Year, 10 AS Month, 9AS Periodunion all
SELECT '40001111222233300000' AS CardNumber,'10/20/2015' AS TransactionDate, 'A' AS Status, 20AS TransactionAmount, 70AS RemainingBalance, 2015 AS Year, 10 AS Month, 10AS Periodunion all
SELECT '40001111222233300000' AS CardNumber,'10/31/2015' AS TransactionDate, 'R' AS Status, -70AS TransactionAmount, 0AS RemainingBalance, 2015 AS Year, 10 AS Month, 10AS Period
SELECT *, SUM(TransactionAmount) OVER ( ORDER BY CardNumber ,TransactionDate
ROWS UNBOUNDED PRECEDING
) AS RemainingBalanceCALC
FROM @TransactionTable
Hope it helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply