Find the matched rows based on redeem value in MySQL-CodePudding

I am implementing the cashback functionality with expiry feature. I am trying to redeem the partial amount based on early expiry date. I've already ordered the rows based on expiry date with the following mysql command.

SELECT * FROM `cashback` WHERE `user_id` = 1 and `used`= 'NO' AND IF(CONCAT(`point_expiry`) !='0000-00-00 00:00:00', `point_expiry` >= NOW(), NOW()) ORDER BY (case when CONCAT(`point_expiry`) = '0000-00-00 00:00:00' then 9999
    else 1
    end) ASC, `point_expiry` ASC

And the output for the following will be

id	amount	point_expiry	used	user_id
3	30	2023-02-24 00:00:00	NO	1
1	20	2023-02-25 00:00:00	NO	1
2	50	0000-00-00 00:00:00	NO	1

Now i want to redeem the value based on the above query result

Let say i want to redeem 35$ for the above result and the expected result will be

id	amount	point_expiry	used	used_amount
3	30	2023-02-24 00:00:00	NO	30
1	20	2023-02-25 00:00:00	NO	5

Here used_amount column represent the specific redeem value($35) redeemed based on amount column

Much appreciate your help!

CodePudding user response：

This uses SUM(amount) OVER(ORDER BY ...) to calculate a running total and compares it to the balance -

SELECT *
FROM (
    SELECT
        `id`,
        `amount`,
        `point_expiry`,
        `used`,
        `amount` - GREATEST(SUM(`amount`) OVER (ORDER BY IF(`point_expiry` = '0000-00-00 00:00:00', 1, 0) ASC, `point_expiry` ASC, id ASC) - /* Here is your amount --> */ 35, 0) AS `used_amount`
    FROM `cashback`
    WHERE (`point_expiry` >= NOW() OR `point_expiry` = '0000-00-00 00:00:00')
    AND `used` = 'NO'
    AND `user_id` = 1
) t
WHERE `used_amount` > 0;