I have a table ‘accounts’. Every day I need to insert automatically new record for all users as opening balance (flag = OP). Opening balance amount is the previous day’s last entry’s cash in hand amount.
How can I write an event for this?
This is my table:
CREATE TABLE `accounts` ( `id` int(11) NOT NULL, `client_id` int(11) DEFAULT NULL, `flag` enum('credit','debit','op','bank') DEFAULT NULL, `department_fee` decimal(14,2) DEFAULT NULL, `service_charges` decimal(14,2) DEFAULT NULL, `gst` decimal(14,2) DEFAULT NULL, `bank_charges` decimal(14,2) DEFAULT NULL, `amount` decimal(14,2) DEFAULT NULL, `cashin_hand` decimal(14,2) DEFAULT NULL, `entry_date` date DEFAULT NULL, `created` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
data
INSERT INTO `accounts` (`id`, `client_id`, `flag`, `department_fee`, `service_charges`, `gst`, `bank_charges`, `amount`, `cashin_hand`, `entry_date`, `created`) VALUES (1, 1, 'credit', '500.00', '0.00', NULL, NULL, '500.00', '500.00', '2018-12-01', '2018-12-01 05:02:56'), (2, 1, 'credit', '20.00', '180.00', NULL, NULL, '200.00', '700.00', '2018-12-01', '2018-12-01 05:03:13'), (3, 1, 'debit', NULL, NULL, NULL, NULL, '300.00', '400.00', '2018-12-01', '2018-12-01 05:03:50'), (4, 1, 'credit', '220.00', '80.00', NULL, NULL, '300.00', '700.00', '2018-12-01', '2018-12-01 05:56:57'), (9, 1, 'op', NULL, NULL, NULL, NULL, '100.00', NULL, '2018-10-31', '2018-10-31 04:06:18'), (10, 1, 'op', NULL, NULL, NULL, NULL, '500.00', NULL, '2018-10-31', '2018-10-31 04:06:47'), (12, 1, 'op', NULL, NULL, NULL, NULL, '700.00', NULL, '2018-10-31', '2018-10-31 06:19:29'), (13, 1, 'op', NULL, NULL, NULL, NULL, '250.00', NULL, '2018-10-31', '2018-10-31 06:23:21'), (14, 1, 'debit', NULL, NULL, NULL, NULL, '570.00', NULL, '2018-10-31', '2018-10-31 06:24:21'), (15, 1, 'credit', '23.00', '10.00', '22.00', '5.00', '60.00', NULL, '2018-10-31', '2018-10-31 07:03:00'), (16, 1, 'credit', '20.00', '180.00', NULL, NULL, '200.00', NULL, '2018-11-30', '2018-11-30 07:39:40'), (17, 1, 'credit', '30.00', '70.00', NULL, NULL, '100.00', NULL, '2018-11-30', '2018-11-30 08:24:08'), (18, 1, 'debit', NULL, NULL, NULL, NULL, '100.00', NULL, '2018-11-30', '2018-11-30 08:25:58'), (19, 1, 'credit', '500.00', '10.00', NULL, NULL, '510.00', '510.00', '2018-12-05', '2018-12-05 06:26:58'), (20, 1, 'op', NULL, NULL, NULL, NULL, '510.00', '510.00', '2019-01-05', '2019-01-05 12:17:33'), (22, 1, 'op', NULL, NULL, NULL, NULL, '510.00', '510.00', '2019-01-07', '2019-01-07 12:24:57'), (23, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '30.00', '2020-09-26', '2020-09-26 14:42:30'), (24, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '90.00', '2020-09-26', '2020-09-26 14:43:07'), (25, 286, 'debit', NULL, NULL, NULL, NULL, '3500.00', '-3410.00', '2020-09-26', '2020-09-26 14:43:18'), (26, 286, 'credit', '2942.00', '0.00', NULL, NULL, '2942.00', '-468.00', '2020-09-26', '2020-09-26 14:43:35'), (27, 286, 'credit', '1994.00', '30.00', NULL, NULL, '2024.00', '1556.00', '2020-09-26', '2020-09-26 14:47:07'), (28, NULL, 'credit', '1004.00', '25.00', NULL, NULL, '1029.00', '1029.00', '2020-09-26', '2020-09-26 11:45:20'), (29, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1596.00', '2020-09-26', '2020-09-26 16:42:19'), (30, 286, 'credit', '1304.00', '25.00', NULL, NULL, '1329.00', '2925.00', '2020-09-26', '2020-09-26 16:56:51'), (31, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '2975.00', '2020-09-26', '2020-09-26 17:04:26'), (32, 286, 'credit', '480.00', '50.00', NULL, NULL, '530.00', '3505.00', '2020-09-26', '2020-09-26 17:09:52'), (33, 286, 'credit', '3402.00', '0.00', NULL, NULL, '3402.00', '3402.00', '2020-09-28', '2020-09-28 10:39:35'), (34, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '3442.00', '2020-09-28', '2020-09-28 17:38:04'), (35, 286, 'credit', '222.00', '15.00', NULL, NULL, '237.00', '3679.00', '2020-09-28', '2020-09-28 17:38:27'), (36, 286, 'credit', '57.00', '15.00', NULL, NULL, '72.00', '3751.00', '2020-09-28', '2020-09-28 17:38:44'), (37, 286, 'credit', '40.00', '10.00', NULL, NULL, '50.00', '3801.00', '2020-09-28', '2020-09-28 17:39:01'), (38, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '3881.00', '2020-09-28', '2020-09-28 17:39:18'), (39, 286, 'credit', '70.00', '10.00', NULL, NULL, '80.00', '3961.00', '2020-09-28', '2020-09-28 17:39:37'), (40, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '4001.00', '2020-09-28', '2020-09-28 17:39:53'), (41, 286, 'credit', '69.90', '20.00', NULL, NULL, '89.90', '4090.90', '2020-09-28', '2020-09-28 17:40:15'), (42, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '4120.90', '2020-09-28', '2020-09-28 17:40:32'), (43, 286, 'credit', '600.00', '50.00', NULL, NULL, '650.00', '5395.90', '2020-09-28', '2020-09-28 17:42:11'), (44, 286, 'credit', '575.00', '50.00', NULL, NULL, '625.00', '5445.90', '2020-09-28', '2020-09-28 17:44:50'), (45, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '5445.90', '2020-09-28', '2020-09-28 17:45:32'), (46, 286, 'credit', '125.00', '50.00', NULL, NULL, '175.00', '5620.90', '2020-09-28', '2020-09-28 17:45:41'), (47, 286, 'credit', '0.00', '80.00', NULL, NULL, '80.00', '5700.90', '2020-09-28', '2020-09-28 17:46:10'), (48, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '5750.90', '2020-09-28', '2020-09-28 17:46:46'), (49, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '5850.90', '2020-09-28', '2020-09-28 17:47:09'), (50, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '5890.90', '2020-09-28', '2020-09-28 17:47:21'), (51, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '5930.90', '2020-09-28', '2020-09-28 17:47:39'), (52, 286, 'credit', '0.00', '33.00', NULL, NULL, '33.00', '5963.90', '2020-09-28', '2020-09-28 17:48:07'), (53, 286, 'credit', '105.00', '20.00', NULL, NULL, '125.00', '6088.90', '2020-09-28', '2020-09-28 17:50:47'), (54, 286, 'credit', '6090.00', '0.00', NULL, NULL, '6090.00', '6090.00', '2020-09-29', '2020-09-29 10:21:25'), (55, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '6130.00', '2020-09-29', '2020-09-29 14:22:10'), (56, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '6190.00', '2020-09-29', '2020-09-29 14:23:25'), (57, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '6220.00', '2020-09-29', '2020-09-29 14:23:46'), (58, 286, 'credit', '0.00', '90.00', NULL, NULL, '90.00', '6310.00', '2020-09-29', '2020-09-29 14:27:57'), (59, 286, 'debit', NULL, NULL, NULL, NULL, '5000.00', '1310.00', '2020-09-29', '2020-09-29 14:36:10'), (60, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1350.00', '2020-09-29', '2020-09-29 17:34:04'), (61, 286, 'credit', '418.00', '15.00', NULL, NULL, '433.00', '1783.00', '2020-09-29', '2020-09-29 17:34:20'), (62, 286, 'credit', '39.90', '25.00', NULL, NULL, '64.90', '1847.90', '2020-09-29', '2020-09-29 17:34:42'), (63, 286, 'credit', '60.00', '10.00', NULL, NULL, '70.00', '1917.90', '2020-09-29', '2020-09-29 17:34:58'), (64, 286, 'credit', '20.00', '10.00', NULL, NULL, '30.00', '1947.90', '2020-09-29', '2020-09-29 17:35:12'), (65, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1987.90', '2020-09-29', '2020-09-29 17:35:27'), (66, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '2017.90', '2020-09-29', '2020-09-29 17:35:40'), (67, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '2097.90', '2020-09-29', '2020-09-29 17:35:55'), (68, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '2137.90', '2020-09-29', '2020-09-29 17:36:15'), (69, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '2167.90', '2020-09-29', '2020-09-29 17:36:49'), (70, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '2217.90', '2020-09-29', '2020-09-29 17:37:07'), (71, 286, 'credit', '0.00', '90.00', NULL, NULL, '90.00', '2307.90', '2020-09-29', '2020-09-29 17:37:22'), (72, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '2407.90', '2020-09-29', '2020-09-29 17:44:08'), (73, 286, 'credit', '0.00', '150.00', NULL, NULL, '150.00', '2557.90', '2020-09-29', '2020-09-29 17:44:26'), (74, 286, 'credit', '0.00', '45.00', NULL, NULL, '45.00', '2602.90', '2020-09-29', '2020-09-29 17:45:21'), (75, 286, 'credit', '2603.00', '0.00', NULL, NULL, '2603.00', '2603.00', '2020-09-30', '2020-09-30 10:18:44'), (76, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '2633.00', '2020-09-30', '2020-09-30 17:45:11'), (77, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '2663.00', '2020-09-30', '2020-09-30 17:45:27'), (78, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '2703.00', '2020-09-30', '2020-09-30 17:45:43'), (79, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '2743.00', '2020-09-30', '2020-09-30 17:46:14'), (80, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '2803.00', '2020-09-30', '2020-09-30 17:46:37'), (81, 286, 'credit', '230.00', '50.00', NULL, NULL, '280.00', '3083.00', '2020-09-30', '2020-09-30 17:47:00'), (82, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '3113.00', '2020-09-30', '2020-09-30 17:47:25'), (83, 286, 'credit', '200.00', '20.00', NULL, NULL, '220.00', '3333.00', '2020-09-30', '2020-09-30 17:48:30'), (84, 286, 'credit', '80.00', '10.00', NULL, NULL, '90.00', '3423.00', '2020-09-30', '2020-09-30 17:48:56'), (85, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '3453.00', '2020-09-30', '2020-09-30 17:49:17'), (86, 286, 'credit', '551.00', '20.00', NULL, NULL, '571.00', '4024.00', '2020-09-30', '2020-09-30 17:49:20'), (87, 286, 'credit', '60.00', '10.00', NULL, NULL, '70.00', '4094.00', '2020-09-30', '2020-09-30 17:49:40'), (88, 286, 'credit', '1257.00', '25.00', NULL, NULL, '1282.00', '5376.00', '2020-09-30', '2020-09-30 17:50:07'), (89, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '5406.00', '2020-09-30', '2020-09-30 17:50:28'), (90, 286, 'credit', '14.00', '46.00', NULL, NULL, '60.00', '5466.00', '2020-09-30', '2020-09-30 17:51:07'), (91, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '5496.00', '2020-09-30', '2020-09-30 17:51:24'), (92, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '5546.00', '2020-09-30', '2020-09-30 17:51:39'), (93, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '5646.00', '2020-09-30', '2020-09-30 17:52:24'), (94, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '5706.00', '2020-09-30', '2020-09-30 17:55:34'), (95, 286, 'credit', '0.00', '20.00', NULL, NULL, '20.00', '5726.00', '2020-09-30', '2020-09-30 17:57:02'), (96, 286, 'credit', '35.00', '115.00', NULL, NULL, '150.00', '150.00', '2020-10-01', '2020-10-01 17:37:00'), (97, 286, 'credit', '123.00', '22.00', NULL, NULL, '145.00', '295.00', '2020-10-01', '2020-10-01 17:37:38'), (98, 286, 'credit', '142.00', '20.00', NULL, NULL, '162.00', '457.00', '2020-10-01', '2020-10-01 17:38:02'), (99, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '497.00', '2020-10-01', '2020-10-01 17:43:08'), (100, 286, 'credit', '175.00', '50.00', NULL, NULL, '225.00', '722.00', '2020-10-01', '2020-10-01 17:43:25'), (101, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '772.00', '2020-10-01', '2020-10-01 17:43:29'), (102, 286, 'credit', '0.00', '60.00', NULL, NULL, '60.00', '832.00', '2020-10-01', '2020-10-01 17:43:45'), (103, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '882.00', '2020-10-01', '2020-10-01 17:44:19'), (104, 286, 'credit', '5726.00', '0.00', NULL, NULL, '5726.00', '6608.00', '2020-10-01', '2020-10-01 17:45:27'), (105, 286, 'credit', '100.00', '20.00', NULL, NULL, '120.00', '120.00', '2020-10-05', '2020-10-05 17:26:54'), (106, 286, 'credit', '549.00', '40.00', NULL, NULL, '589.00', '709.00', '2020-10-05', '2020-10-05 17:27:47'), (107, 286, 'credit', '220.00', '20.00', NULL, NULL, '240.00', '949.00', '2020-10-05', '2020-10-05 17:28:04'), (108, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '989.00', '2020-10-05', '2020-10-05 17:28:17'), (109, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1029.00', '2020-10-05', '2020-10-05 17:28:33'), (110, 286, 'credit', '0.00', '40.00', NULL, NULL, '40.00', '1069.00', '2020-10-05', '2020-10-05 17:28:52'), (111, 286, 'credit', '234.00', '20.00', NULL, NULL, '254.00', '1323.00', '2020-10-05', '2020-10-05 17:29:33'), (112, 286, 'credit', '750.00', '100.00', NULL, NULL, '850.00', '2173.00', '2020-10-05', '2020-10-05 17:29:39'), (113, 286, 'credit', '72.00', '15.00', NULL, NULL, '87.00', '2260.00', '2020-10-05', '2020-10-05 17:29:49'), (114, 286, 'credit', '445.00', '40.00', NULL, NULL, '485.00', '2745.00', '2020-10-05', '2020-10-05 17:30:03'), (115, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '2825.00', '2020-10-05', '2020-10-05 17:30:07'), (116, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '2865.00', '2020-10-05', '2020-10-05 17:30:21'), (117, 286, 'credit', '445.00', '40.00', NULL, NULL, '485.00', '3350.00', '2020-10-05', '2020-10-05 17:30:37'), (118, 286, 'credit', '555.00', '30.00', NULL, NULL, '585.00', '3935.00', '2020-10-05', '2020-10-05 17:30:46'), (119, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '3985.00', '2020-10-05', '2020-10-05 17:31:09'), (120, 286, 'credit', '485.00', '40.00', NULL, NULL, '525.00', '4510.00', '2020-10-05', '2020-10-05 17:31:13'), (121, 286, 'credit', '20.00', '10.00', NULL, NULL, '30.00', '4540.00', '2020-10-05', '2020-10-05 17:31:23'), (122, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '4590.00', '2020-10-05', '2020-10-05 17:31:42'), (123, 286, 'credit', '485.00', '40.00', NULL, NULL, '525.00', '5115.00', '2020-10-05', '2020-10-05 17:32:00'), (124, 286, 'credit', '100.00', '20.00', NULL, NULL, '120.00', '5235.00', '2020-10-05', '2020-10-05 17:32:22'), (125, 286, 'credit', '445.00', '40.00', NULL, NULL, '485.00', '5720.00', '2020-10-05', '2020-10-05 17:33:54'), (126, 286, 'debit', NULL, NULL, NULL, NULL, '485.00', '5235.00', '2020-10-05', '2020-10-05 17:34:25'), (127, 286, 'credit', '6608.00', '0.00', NULL, NULL, '6608.00', '11943.00', '2020-10-05', '2020-10-05 17:37:14'), (128, 286, 'credit', '80.00', '20.00', NULL, NULL, '100.00', '11926.00', '2020-10-05', '2020-10-05 17:37:45'), (129, 286, 'credit', '360.00', '40.00', NULL, NULL, '400.00', '12343.00', '2020-10-05', '2020-10-05 17:44:21'), (130, 286, 'credit', '14.00', '46.00', NULL, NULL, '60.00', '12403.00', '2020-10-05', '2020-10-05 17:44:49'), (131, 286, 'debit', NULL, NULL, NULL, NULL, '4000.00', '8403.00', '2020-10-05', '2020-10-05 17:53:31'), (132, 286, 'debit', NULL, NULL, NULL, NULL, '4000.00', '4403.00', '2020-10-05', '2020-10-05 17:53:51'), (133, 286, 'debit', NULL, NULL, NULL, NULL, '340.00', '4063.00', '2020-10-05', '2020-10-05 17:55:45'), (134, 286, 'debit', NULL, NULL, NULL, NULL, '220.00', '3843.00', '2020-10-05', '2020-10-05 17:56:43'), (135, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '100.00', '2020-10-06', '2020-10-06 17:12:17'), (136, 286, 'credit', '445.00', '40.00', NULL, NULL, '485.00', '585.00', '2020-10-06', '2020-10-06 17:13:30'), (137, 286, 'credit', '14.00', '46.00', NULL, NULL, '60.00', '645.00', '2020-10-06', '2020-10-06 17:19:41'), (138, 286, 'credit', '0.00', '25.00', NULL, NULL, '25.00', '670.00', '2020-10-06', '2020-10-06 17:20:23'), (139, 286, 'debit', NULL, NULL, NULL, NULL, '3500.00', '-2830.00', '2020-10-06', '2020-10-06 17:21:08'), (140, 286, 'credit', '3843.00', '0.00', NULL, NULL, '3843.00', '1013.00', '2020-10-06', '2020-10-06 17:21:36'), (141, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '1063.00', '2020-10-06', '2020-10-06 17:22:17'), (142, 286, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '1093.00', '2020-10-06', '2020-10-06 17:22:30'), (143, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '1173.00', '2020-10-06', '2020-10-06 17:22:36'), (144, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1213.00', '2020-10-06', '2020-10-06 17:23:03'), (145, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1253.00', '2020-10-06', '2020-10-06 17:23:23'), (146, 286, 'credit', '0.00', '40.00', NULL, NULL, '40.00', '1293.00', '2020-10-06', '2020-10-06 17:23:41'), (147, 286, 'credit', '160.00', '20.00', NULL, NULL, '180.00', '1473.00', '2020-10-06', '2020-10-06 17:23:58'), (148, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '1553.00', '2020-10-06', '2020-10-06 17:24:22'), (149, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1593.00', '2020-10-06', '2020-10-06 17:24:40'), (150, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1633.00', '2020-10-06', '2020-10-06 17:25:07'), (151, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '1683.00', '2020-10-06', '2020-10-06 17:25:27'), (152, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '1733.00', '2020-10-06', '2020-10-06 17:27:45'), (153, 286, 'debit', NULL, NULL, NULL, NULL, '500.00', '-500.00', '2020-10-07', '2020-10-07 14:44:10'), (154, 286, 'credit', '14.00', '46.00', NULL, NULL, '60.00', '-440.00', '2020-10-07', '2020-10-07 16:59:13'), (155, 286, 'credit', '0.00', '100.00', NULL, NULL, '100.00', '-340.00', '2020-10-07', '2020-10-07 16:59:42'), (156, 286, 'credit', '200.00', '50.00', NULL, NULL, '250.00', '-90.00', '2020-10-07', '2020-10-07 17:01:38'), (157, 286, 'credit', '127.00', '23.00', NULL, NULL, '150.00', '60.00', '2020-10-07', '2020-10-07 17:02:22'), (158, 286, 'credit', '8.00', '10.00', NULL, NULL, '18.00', '78.00', '2020-10-07', '2020-10-07 17:10:39'), (159, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '118.00', '2020-10-07', '2020-10-07 17:10:54'), (160, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '158.00', '2020-10-07', '2020-10-07 17:11:07'), (161, 286, 'credit', '120.00', '80.00', NULL, NULL, '200.00', '358.00', '2020-10-07', '2020-10-07 17:11:32'), (162, 286, 'credit', '100.00', '50.00', NULL, NULL, '150.00', '508.00', '2020-10-07', '2020-10-07 17:11:48'), (163, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '538.00', '2020-10-07', '2020-10-07 17:12:04'), (164, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '568.00', '2020-10-07', '2020-10-07 17:12:20'), (165, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '618.00', '2020-10-07', '2020-10-07 17:12:20'), (166, 286, 'credit', '7.00', '23.00', NULL, NULL, '30.00', '648.00', '2020-10-07', '2020-10-07 17:12:38'), (167, 286, 'credit', '60.00', '20.00', NULL, NULL, '80.00', '728.00', '2020-10-07', '2020-10-07 17:12:58'), (168, 286, 'credit', '160.00', '20.00', NULL, NULL, '180.00', '908.00', '2020-10-07', '2020-10-07 17:13:16'), (169, 286, 'credit', '1733.00', '0.00', NULL, NULL, '1733.00', '2641.00', '2020-10-07', '2020-10-07 17:19:42'), (170, 286, 'credit', '2641.00', '0.00', NULL, NULL, '2641.00', '2641.00', '2020-10-08', '2020-10-08 10:39:54'), (171, 286, 'debit', NULL, NULL, NULL, NULL, '2200.00', '441.00', '2020-10-08', '2020-10-08 13:38:02'), (172, 286, 'debit', NULL, NULL, NULL, NULL, '410.00', '31.00', '2020-10-08', '2020-10-08 13:38:52'), (173, 262, 'debit', NULL, NULL, NULL, NULL, '5500.00', '-5500.00', '2020-10-08', '2020-10-08 17:00:22'), (174, 262, 'credit', '115.00', '30.00', NULL, NULL, '145.00', '-5355.00', '2020-10-08', '2020-10-08 17:00:36'), (175, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-4845.00', '2020-10-08', '2020-10-08 17:00:49'), (176, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-4335.00', '2020-10-08', '2020-10-08 17:01:04'), (177, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-3825.00', '2020-10-08', '2020-10-08 17:01:20'), (178, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-3315.00', '2020-10-08', '2020-10-08 17:01:34'), (179, 262, 'credit', '480.00', '30.00', NULL, NULL, '510.00', '-2805.00', '2020-10-08', '2020-10-08 17:01:52'), (180, 262, 'credit', '0.00', '30.00', NULL, NULL, '30.00', '-2775.00', '2020-10-08', '2020-10-08 17:04:53'), (181, 262, 'credit', '80.00', '10.00', NULL, NULL, '90.00', '-2685.00', '2020-10-08', '2020-10-08 17:05:06'), (182, 262, 'credit', '747.00', '15.00', NULL, NULL, '762.00', '-1923.00', '2020-10-08', '2020-10-08 17:05:19'), (183, 262, 'credit', '120.00', '10.00', NULL, NULL, '130.00', '-1793.00', '2020-10-08', '2020-10-08 17:05:34'), (184, 262, 'credit', '180.00', '15.00', NULL, NULL, '195.00', '-1598.00', '2020-10-08', '2020-10-08 17:06:09'), (185, 262, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '-1548.00', '2020-10-08', '2020-10-08 17:06:24'), (186, 286, 'credit', '119.00', '391.00', NULL, NULL, '510.00', '541.00', '2020-10-08', '2020-10-08 17:07:45'), (187, 286, 'credit', '0.00', '20.00', NULL, NULL, '20.00', '561.00', '2020-10-08', '2020-10-08 17:09:33'), (188, 262, 'credit', '91.00', '349.00', NULL, NULL, '440.00', '-1108.00', '2020-10-08', '2020-10-08 17:11:00'), (189, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '641.00', '2020-10-08', '2020-10-08 17:16:40'), (190, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '681.00', '2020-10-08', '2020-10-08 17:16:55'), (191, 286, 'credit', '550.00', '40.00', NULL, NULL, '590.00', '1271.00', '2020-10-08', '2020-10-08 17:18:18'), (192, 286, 'credit', '120.00', '80.00', NULL, NULL, '200.00', '1471.00', '2020-10-08', '2020-10-08 17:18:39'), (193, 286, 'credit', '14.00', '66.00', NULL, NULL, '80.00', '1551.00', '2020-10-08', '2020-10-08 17:18:55'), (194, 286, 'credit', '7.00', '33.00', NULL, NULL, '40.00', '1591.00', '2020-10-08', '2020-10-08 17:19:11'), (195, 286, 'credit', '0.00', '200.00', NULL, NULL, '200.00', '1791.00', '2020-10-08', '2020-10-08 17:19:35'), (196, 286, 'credit', '0.00', '50.00', NULL, NULL, '50.00', '1841.00', '2020-10-08', '2020-10-08 17:22:09'), (197, 1, 'credit', '70.00', '25.00', '5.00', '2.00', '102.00', '102.00', '2020-10-09', '2020-10-09 17:12:44'), (198, 1, 'credit', '60.00', '25.00', '4.00', '1.00', '90.00', '192.00', '2020-10-09', '2020-10-09 17:13:06'), (199, 1, 'debit', NULL, NULL, NULL, NULL, '100.00', '92.00', '2020-10-09', '2020-10-09 17:13:21'), (200, 1, 'credit', '200.00', '25.00', '4.00', '2.00', '231.00', '323.00', '2020-10-09', '2020-10-09 17:15:35');
Advertisement
Answer
In maria db there is a add new event form,and we can set the event there if you have the correct query.below was my query.Now it working well.
INSERT INTO accounts ( client_id, flag, amount, cashin_hand, entry_date, created, modified) SELECT A1.client_id,'OP' ,A1.cashin_hand,A1.cashin_hand,current_date(),current_timestamp(),current_timestamp() FROM (SELECT client_id, Max(id) id FROM accounts GROUP BY client_id) A INNER JOIN accounts A1 on A.client_id = A1.client_id AND A.id = A1.id