Issue
I'm working on a SQL table that save money transaction for every day. this is my table design:
CREATE TABLE `transaction` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`member_id` int(11) NOT NULL,
`name` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
`balance_lastMonth` int(11) NOT NULL,
`income` int(11) NOT NULL,
`outcome` int(11) NOT NULL,
`balance` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `member_id` (`member_id`),
CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`)
) ENGINE=InnoDB CHARSET=latin1
the balance field formula : balance_lastMonth
+ income
- outcome
and balance_lastMonth is balance in the last month
is it possible to achieve it in one table? if yes how to do it? or may be there is better way to do it. i'm using 10.4.6-MariaDB.
Solution
You can calculate the balance using a query:
select t.*,
sum(income - outcome) over (partition by member_id order by date) as balance,
sum(income - outcome) over (partition by member_id order by date) - (income - outcome) as balance_lastmonth
from transaction t;
The simplest thing to do is to encapsulate this in a view and just use that.
If you actually want to store the results in the table, you'll need to use a trigger. I don't recommend that approach unless you have some sort of requirement that the balances be stored.
Answered By - Gordon Linoff
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.