Ioannis Kokkinis Ioannis Kokkinis - 24 days ago 8
MySQL Question

Transforming a cumulative sequence of values by date to the non cumulative values

Is it possible with mysql, to get the sum for a given date range of cumulative values?

Assume data are like this (cumulative week starting on Saturday -Day column is not needed , just there to show the change and easier to visualize):

Date Day Value
03/11/2016 Thursday 15
04/11/2016 Friday 18
05/11/2016 Saturday 4
06/11/2016 Sunday 6
07/11/2016 Monday 8
08/11/2016 Tuesday 10
09/11/2016 Wednesday 13
10/11/2016 Thursday 17
11/11/2016 Friday 18
12/11/2016 Saturday 2
13/11/2016 Sunday 4
14/11/2016 Monday 6
and so on...


So for example If I was asking for the result from 03/11/2016 to 04/11/2016 I would expect to see : 3 (18-15)
from 03/11/2016 to 07/11/2016 : (18-15)+4+(6-4)+(8-6) = 11

or to put it better the above table should be transformed to :

Date Day Value
03/11/2016 Thursday -
04/11/2016 Friday 3
05/11/2016 Saturday 4
06/11/2016 Sunday 2
07/11/2016 Monday 2
08/11/2016 Tuesday 2
09/11/2016 Wednesday 3
10/11/2016 Thursday 4
11/11/2016 Friday 1
12/11/2016 Saturday 2
13/11/2016 Sunday 2
14/11/2016 Monday 2


In php or other languages I would just go like (pseudocode):

If (day != Saturday) {value = currentvalue-yesterdayvalue} else {value = currentvalue)


Ideally I would like to derive this one in mysql

Answer
SELECT x.*
     , CASE WHEN WEEKDAY(x.date) = 5 
            THEN x.value 
            ELSE x.value-y.value END x 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.date = x.date - INTERVAL 1 DAY;