CODEWITHSUNDEEP
mysqlaggregate
Paul
Paul

Reputation: 94

MySQL how to present day results (starting value, total change and day-end value from table

I have this table (have a look on SQLFiddle) In previous steps the record number has been determined and the values for "PrevVal" and "NewVal" have been calculated.

The record's end value ("NewVal"), becomes the next record's starting value ("PrevVal")

I would like to condense the table in such a way that there is only one record per day, containing:

The desired result can be seen in the demo table "ChangesPerDayCondensed"

Who can help me solve this (a stored procedure is OK). Thnx

Upvotes: 0

Views: 80

Answers (2)

Jan Zeiseweis
Jan Zeiseweis

Reputation: 3738

I'd do it this way:

First create a lookup for each day (find first and lasts ReqNo) and then join two times to the Daily table and calculate the changes:

SELECT first_.MyDate,
       first_.PrevVal AS StartOfDay,
       last_.NewVal AS EndOfDay,
       (last_.NewVal - first_.PrevVal) AS TotalChange
FROM
  (SELECT mpd1.MyDate,
          max(mpd1.RecNo) AS first_rec_no,
          min(mpd1.RecNo) AS last_rec_no
   FROM MutationsPerDay mpd1
   GROUP BY MyDate) AS lo
JOIN MutationsPerDay AS first_ ON lo.first_rec_no = first_.RecNo
JOIN MutationsPerDay AS last_ ON lo.last_rec_no = last_.RecNo

Explanation: What you actually want is: For every day the first and the last value (and the difference). So what you need to find first is for every date the id of the first and the last value: 

SELECT mpd1.MyDate,
      max(mpd1.RecNo) AS first_rec_no,
      min(mpd1.RecNo) AS last_rec_no
FROM MutationsPerDay mpd1
GROUP BY MyDate

----------------------------------------------------
| MyDate              | first_rec_no | last_rec_no |
----------------------------------------------------
| 2016-12-05 00:00:00 | 16           | 13          |
| 2016-12-07 00:00:00 | 12           | 12          |
| 2016-12-12 00:00:00 | 11           | 8           |
| 2016-12-14 00:00:00 | 7            | 7           |
| 2016-12-20 00:00:00 | 6            | 6           |
| 2016-12-21 00:00:00 | 5            | 4           |
| 2016-12-28 00:00:00 | 3            | 3           |
| 2016-12-29 00:00:00 | 2            | 2           |
| 2016-12-30 00:00:00 | 1            | 1           |
----------------------------------------------------

Then you can use these first and last id's to find the corresponding values in the source table. For example for the 2016-12-21 you'd get the rows with the id's first: 5 and last: 4

The PrevVal record no 5 represents the first value you have seen at this day and NewVal in record no 4 represents the last value you have seen at this day. If you subtract them you'll get the change for this day.

I hope this clarifies the methodology a bit.

Upvotes: 1

stk
stk

Reputation: 114

I am a little confused whey the record numbers are going the opposite way. But neverthless you could solve this by evaluating the starting value and sum of mutations separatately and then adding them all to come up with ending value.. Ordering the results descending as the record number again needs to be lower for a higher date. 

insert into ChangesPerDayCondensed 
select @recrd:=@recrd+1, a.MyDate, b.PrevVal, a.Mutation, b.PrevVal+a.Mutation 
from 
(select MyDate, sum(Mutation) as   Mutation from MutationsPerDay group by MyDate) a, 
(select b.MyDate, b.PrevVal from (select MyDate, max(RecNo) as RecNo from    MutationsPerDay group by MyDate) a, MutationsPerDay b where a.RecNo = b.RecNo) b,
(select @recrd:=0) c
where a.MyDate = b.MyDate order by MyDate desc;

Upvotes: 1

Related Questions