CODEWITHSUNDEEP
mysqlsql
I. D.
I. D.

Reputation: 11

SQL peak value per day

i have a following table:

   id |  booking_time       | art   | weight
   ---+---------------------+-------+------
    1 | 2017-06-18 10:34:09 | wood  |  1000
    2 | 2017-06-18 11:31:11 | wood  |  2000
    3 | 2017-06-18 14:11:25 | stone |  1000
    4 | 2017-06-18 16:47:37 | wood  |  -300
    5 | 2017-06-19 10:49:21 | wood  |   100
    6 | 2017-06-19 12:41:02 | wood  | -1000
    7 | 2017-06-19 12:49:54 | wood  |   200

what am i trying to get is peak value in stock per day:

   booking_day |  art  | peak
   ------------+-------+-------
    2017-06-18 | wood  | 3000
    2017-06-16 | stone | 1000
    2017-06-19 | wood  | 2800

so for 18.06 highest amount of wood in stock would be 3000 (1000 + 2000); at the end of the day only 2700 (1000 + 2000 - 300) left

for 19.06 we start at 2700 wood from yesterday reaching peak with 2800 (2700 + 100); amount left at end of the day would be 2000 (2700 + 100 - 1000 + 200)

is it even possible with SQL? maybe i need another table to store daily amounts or something like that

Upvotes: 1

Views: 265

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You need to calculate a cumulative sum and then aggregate:

select date(booking_time), art, max(running_stock)
from (select t.*,
             (@s := if(@a = t.art, @s + weight,
                       if @a := t.art, weight, weight)
                      )
             ) as running_stock
      from t cross join
           (select @s := 0, @a := 0) params
      order by t.art, t.booking_time
     ) t
group by date(booking_time), art;

Upvotes: 2

Related Questions