Mippy Mippy - 4 months ago 17
SQL Question

Update row order by date

I've asked this question in the morning, but did not get any answer for this. So I delete previous one, and ask this again cause I am getting stuck for a long while. Hope you guys can help me.

I have a table named

overtime
like following:

| total | remain | t_date |
|-------|--------|---------------------|
| 3 | 0 | 2016-01-01 12:20:00 |
| 4 | 0 | 2016-02-01 13:10:00 |
| 2 | 0 | 2016-03-01 14:40:00 |
| 3 | 0 | 2016-04-01 10:20:00 |
| 5 | 2 | 2016-05-01 17:20:00 |


I want to update column remain
order by t_date desc
, also I have an input parameter, assume it is $h = 9, the expected result is:

| total | remain | t_date |
|-------|--------|---------------------|
| 5 | 5 | 2016-05-01 17:20:00 | -- remain will be updated to 5 cause total = 5, then $h(6) = $h(9) - (total(5) - remain(2))
| 3 | 3 | 2016-04-01 10:20:00 | -- remain will be updated to 3 cause total = 3, then $h(3) = $h(6) - (total(3) - remain(0))
| 2 | 2 | 2016-03-01 14:40:00 | -- remain will be updated to 2 cause total = 2, then $h(1) = $h(3) - (total(2) - remain(0))
| 4 | 1 | 2016-02-01 13:10:00 | -- remain will be updated to 1 cause $h only has 1, then $h will be 0
| 3 | 0 | 2016-01-01 12:20:00 | -- cause $h = 0, this row has no need to be updated


Edited:
The sample data is like above, what I want to do is update column
remain
, and
remain
'value bases on total and an input parameter(just assume it is 9):


  • update order is
    order by t_date desc
    . For example, I must update
    2016-05-01 17:20:00
    row first, then
    2016-04-01 10:20:00
    , then
    2016-03-01 14:40:00
    and so on...

  • Parameter is 9, it will be allocated to every row, and
    remain
    should be updated to
    total
    's value. For example, the first row
    2016-05-01 17:20:00
    ,
    total
    = 5 and
    remain
    = 2, so
    remain
    will be updated to 5, and parameter will minus
    total
    -
    remain
    , it will be 6 and do the next row's allocation, till row
    2016-02-01 13:10:00
    , the paramter is 1, so this row's
    remain
    only needs to updated to 1. And another row will have no need to updated.



Here is SQLFiddle demo.

If there is any unclear point for question, please leave a comment, I can explain that.

Any help is appreciated. Thanks in advance.

Answer

SELECT QUERY:

    SELECT 
    *,
    IF((IF(@h <= 0,0,IF(@h >= total,total, @h)) + remain) <= total, (IF(@h <= 0,0,IF(@h >= total,total, @h)) + remain), (IF(@h <= 0,0,IF(@h >=  total,total, @h)))) allocated,
    @h := @h - (total - remain)
    FROM overtime
    CROSS JOIN (SELECT @h := 9) var
    ORDER BY t_date DESC

Demo of SELECT

UPDATE QUERY:

UPDATE 
overtime OT 
INNER JOIN 

(
        SELECT 
        *,
        IF((IF(@h <= 0,0,IF(@h >= total,total, @h)) + remain) <= total, (IF(@h <= 0,0,IF(@h >= total,total, @h)) + remain), (IF(@h <= 0,0,IF(@h >=  total,total, @h)))) allocated,
        @h := @h - (total - remain)
        FROM overtime
        CROSS JOIN (SELECT @h := 9) var
        ORDER BY t_date DESC
) AS t
ON OT.t_date = t.t_date
SET OT.remain = t.allocated;

WORKING DEMO

Demo shows the table data sorted by descending order of date after being updated by the above update query.

More:

See Demo for h=2