MySQL day wise opening balance and closing balance

Question

I am developing a accounting software. Want to maintain day wise opening and closing balance. Opening balance for a date is closing balance of previous date. Whenever a transaction for a specific date is added / updated opening and closing balance of that date and all dates after that should be updated into table. Table structure I created is

CREATE TABLE IF NOT EXISTS `account_ledger_day_balances` (
  `acdb_id` int(11) NOT NULL AUTO_INCREMENT,
  `account_ledger_id` int(11) NOT NULL,
  `transaction_date` date NOT NULL,
  `company_id` int(11) NOT NULL,
  `financial_year` int(11) NOT NULL,
  `day_amount` float NOT NULL,
  `opening_balance` float NOT NULL,
  `closing_balance` float GENERATED ALWAYS AS ((`day_amount` + `opening_balance`)) VIRTUAL NOT NULL,
  PRIMARY KEY (`acdb_id`),
  UNIQUE KEY `unique` (`account_ledger_id`,`transaction_date`,`company_id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
COMMIT;

INSERT INTO `account_ledger_day_balances` (`acdb_id`, `account_ledger_id`, `transaction_date`, `company_id`, `financial_year`, `day_amount`, `opening_balance`) VALUES 
   (NULL, '1012', '2020-06-20', '1', '0', '-164670', '0'), 
   (NULL, '1012', '2020-06-30', '1', '0', '-53957.6', '0'), 
   (NULL, '1012', '2020-07-17', '1', '0', '-21000', '0'), 
   (NULL, '1012', '2020-09-01', '1', '0', '-1667.7', '0'), 
   (NULL, '1012', '2020-09-09', '1', '0', '-47640', '0'), 
   (NULL, '1012', '2020-09-10', '1', '0', '-36912', '0'), 
   (NULL, '1012', '2020-09-16', '1', '0', '-9600', '0'), 
   (NULL, '1012', '2020-09-21', '1', '0', '-25680', '0'), 
   (NULL, '1012', '2020-09-28', '1', '0', '-6000', '0'), 
   (NULL, '1012', '2020-10-14', '1', '0', '-6930', '0'), 
   (NULL, '1012', '2020-10-15', '1', '0', '-7800', '0'), 
   (NULL, '1012', '2020-10-21', '1', '0', '-7800', '0'), 
   (NULL, '1012', '2020-10-22', '1', '0', '-12000', '0'), 
   (NULL, '1012', '2020-11-20', '1', '0', '-540', '0')

After every transaction I update day_amount into this table but not able to update opening balance of all dates of transaction_date and after that.

Tried following query and it updates but not taking closing balance.

UPDATE `account_ledger_day_balances` SET opening_balance = (SELECT closing_balance1 FROM  
        (SELECT (day_amount + opening_balance) as closing_balance1, account_ledger_id, transaction_date FROM account_ledger_day_balances) as prev_day 
         WHERE prev_day.account_ledger_id = account_ledger_day_balances.account_ledger_id AND   
         prev_day.transaction_date < account_ledger_day_balances.transaction_date  
         ORDER BY prev_day.transaction_date DESC LIMIT 0,1) WHERE `account_ledger_id` = 1012

Output what I want is as below

Date              Day Amount          Opening Balance     Closing Balance
2020-06-20        -164670             0                   -164670
2020-06-30        -53957.6            -164670             -2,18,627.6
2020-07-17        21000               -2,18,627.6         -1,97,627.6

Should Float or Decimal data type be used for dollar amounts? [The answer is easy. Never floats. NEVER !](https://stackoverflow.com/questions/61872/use-float-or-decimal-for-accounting-application-dollar-amount#:~:text=Float%20stores%20an%20approximate%20value,scientific%20measurements%20should%20use%20float.&text=When%20multiplying%20a%20non%20integer,precision%20while%20floats%20do%20not.) — Luuk, Feb 01 '21 at 07:23
`comapny_id` is using `int(11)`. [Quora](https://www.quora.com/How-many-companies-exist-in-the-world) says that there are about 155M companies, so `int(11)` seems a bit overrated? the same goes for `financial_year`. — Luuk, Feb 01 '21 at 07:29
Thanks Luuk for editing question format. can change data types but now looking for solution to update opening balance of date as closing balance of previous date for multiple rows in single query or may be store procedure — Isha Technohub, Feb 01 '21 at 07:34
Note: DECIMAL must be used, FLOAT not. @Luuk gave the impression to not use DECIMAL, this is false: DECIMAL is fixed-point and perfectly suitable for currency. — geertjanvdk, Feb 01 '21 at 08:05
@Luuk I agree that INT is unnecessary in the majority of these instances, but note that the number in parentheses is largely immaterial in an INT declaration. — Strawberry, Feb 01 '21 at 08:10

score 1 · Accepted Answer · answered Feb 01 '21 at 08:42

1

Unrelated of what you are building (I honestly am not sure what you are doing there), the UPDATE statement that you are executing will always return NULL for the last date 2020-06-20. So you'll have to figure out how to handle.

A good way to debug such things is to break the UPDATE as SELECT statements. I execute the following with the MIN/MAX date:

SELECT closing_balance1
FROM (SELECT (day_amount + opening_balance) as closing_balance1,
             account_ledger_id,
             transaction_date
      FROM account_ledger_day_balances) as prev_day
WHERE prev_day.account_ledger_id = 1012
  AND prev_day.transaction_date < '2020-06-20'
ORDER BY prev_day.transaction_date DESC
LIMIT 0,1

That returns NULL so SET opening_balance = NULL will fail.

On another note, and important: do not use FLOAT, but DECIMAL (see comments), and do not use MyISAM, use InnoDB! (especially when using transactions).

answered Feb 01 '21 at 08:42

geertjanvdk

3,440
24
26

thanks for suggestions. Will implement those. I will try to explain what I am trying to do. You may suggest a better solution. I am developing sales, purchase and accounting software. Each sale, purchase, payment receipt, payment given will be entered into transactions table. Transactions will be done against multiple ledgers. We want date wise ledger opening and closing balance. There can be multiple transactions of a ledger on any specific date. If a transaction a date is updated or added or deleted then opening and closing balance of that date and all further dates should be updated. – Isha Technohub Feb 01 '21 at 10:00
if I run above query it updates first 1-2 rows properly but later rows it updates with old data of opening balance column, not updated opening balance – Isha Technohub Feb 01 '21 at 10:02
OK, it's a bit too complicated to get done in one question on StackOverflow. However, if the opening balance is he closing balance of yesterday, why storing the opening balance at all? It must be always equal right? Maybe it's a complexity you can get rid off? If you want to keep it dynamic, why storing the opening/closing balance at all, you can always retrieve it using SUM (yes, more calculation, bit it always accurate). Just ideas and pointers. – geertjanvdk Feb 01 '21 at 10:08
by storing I thought it will faster to show opening balance of any date any where instead of calculating every time. Specially in reports. Is there any better way to store or calculating dynamical is best solution? – Isha Technohub Feb 01 '21 at 10:35
Whether it is better or worse, is up to you. I would first do it dynamically, optimize later. There is something called 'VIEWS' in SQL, maybe that is something you can investigate. – geertjanvdk Feb 01 '21 at 10:57
already implemented dynamically. Trying this approach now. – Isha Technohub Feb 01 '21 at 12:42

MySQL day wise opening balance and closing balance

1 Answers1