SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `ps_bonus_account` ( `id_bonusaccount` int(11) NOT NULL, `id_customer` int(10) UNSIGNED NOT NULL, `id_order` int(10) UNSIGNED NOT NULL, `id_cart_rule` int(10) UNSIGNED NOT NULL, `points` decimal(20,6) NOT NULL DEFAULT '0.000000', `comment` text NOT NULL, `status` int(10) UNSIGNED NOT NULL, `paid` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', `type` varchar(20) NOT NULL, `date_add` datetime DEFAULT NULL, `date_bonus_to` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `ps_bonus_account` (`id_bonusaccount`, `id_customer`, `id_order`, `id_cart_rule`, `points`, `comment`, `status`, `paid`, `type`, `date_add`, `date_bonus_to`) VALUES (220, 518, 0, 0, '100.000000', 'За регистрацию', 1, 1, 'no_order', '2020-10-09 00:00:00', '2021-11-09 23:59:59'), (218, 518, 278, 50, '5.000000', 'Бонусы за заказ', 1, 1, 'order', '2021-11-09 21:45:16', '2022-12-09 23:59:59'), (219, 518, 278, 50, '10.000000', 'Оплата заказа', 2, 1, 'order', '2021-11-09 21:45:10', '2022-12-09 21:45:10'), (218, 518, 278, 50, '5.000000', 'Бонусы за заказ', 1, 1, 'order', '2021-11-09 21:45:16', '2022-12-09 23:59:59'), (219, 518, 278, 50, '7.000000', 'Оплата заказа', 2, 1, 'order', '2021-11-09 21:45:10', '2022-12-09 21:45:10') ; SELECT `ps_bonus_account`.`id_customer`, SUM(IF(`status` = 1, `points`, - `points`) - IF( `status` = 1 AND `date_bonus_to` < NOW(), GREATEST(0, `ps_bonus_account`.`points` - ( SELECT SUM(`points`) FROM `ps_bonus_account` `used` WHERE `used`.`id_customer` = `ps_bonus_account`.`id_customer` AND `status` = 2 AND `paid` = 1 AND `used`.`date_add` < `ps_bonus_account`.`date_bonus_to` )), 0)) `balance` FROM `ps_bonus_account` WHERE `paid` = 1 GROUP BY `ps_bonus_account`.`id_customer` ;