I'm working on one project and now I'm in blind spot, trying to resolve this..
I have MySQL table with name x_world with some columns. Here is an example:
internalid name population date
1 John 100 2014-05-01
2 Tommy 250 2014-05-01
3 Paul 200 2014-05-01
4 Holly 700 2014-05-01
5 John 100 2014-05-02
6 Tommy 240 2014-05-02
7 Paul 201 2014-05-02
8 Holly 680 2014-05-02
9 John 100 2014-05-03
10 Tommy 230 2014-05-03
11 Paul 202 2014-05-03
12 Holly 799 2014-05-03
What i need, is get this result:
name population (+-1d) (+-3d)
John 100 0 0
Tommy 230 -10 -20
Paul 202 +1 +2
Here is explanation about what i mean and need:
- Max allowed population change (grow) is 2 (including 2). If population grows over 2, dont display this row in result.
- If population is only descending (no matter how much), it's okay, display this row.
- Combination of both above conditions: if population descends one day (no matter how much), and then grows (no more then 2) - it's okay too, display this row.
- If population does not change during these 3 days, it's okay, display this row.
In addition:
- The value of population displayed in result equals to the last known value (latest date).
- The two values (+-1d) and (+-3d) are calculated.
I'm beginner in PHP and MySQL. I'm familiar to very basics of both, however, the MySQL part looks insane for me :( I appreciate every piece of help. And finally, sorry for my bad english. I did my best :)