Started learning PHP and MySQL yesterday and have managed to create two tables, insert rows and then display that data on a web page using various different groupings. Now I need to do a calculation based on data in the two tables and write the result back to one of the tables.
I'm trying to figure out how to perform an equation for a row with a date in table A using a range of values associated with a range of dates in table B. The two dates are in the format YYYY-MM-DD, but the days mostly do not match, so I need match on the month.
Here's the two tables I have:
Table A (user)
+----+----------+------------+-------------+
| id | username | start-date | bench-value |
+----+----------+------------+-------------+
| 1 | tim | 2010-03-04 | |
+----+----------+------------+-------------+
| 2 | jim | 2010-05-30 | |
+----+----------+------------+-------------+
| 3 | fred | 2010-06-12 | |
+----+----------+------------+-------------+
| 4 | sam | 2010-08-16 | |
+----+----------+------------+-------------+
| 5 | jane | 2010-10-21 | |
+----+----------+------------+-------------+
| 6 | ella | 2010-10-21 | |
+----+----------+------------+-------------+
| 7 | bob | 2011-01-24 | |
+----+----------+------------+-------------+
Table B (benchmark)
+----+------------+---------+
| id | start-date | value |
+----+------------+---------+
| 1 | 2010-01-31 | 1173.19 |
+----+------------+---------+
| 2 | 2010-02-28 | 1199.85 |
+----+------------+---------+
| 3 | 2010-03-31 | 1264.91 |
+----+------------+---------+
| 4 | 2010-04-30 | 1263.43 |
+----+------------+---------+
| 5 | 2010-05-31 | 1211.36 |
+----+------------+---------+
| 6 | 2010-06-30 | 1187.32 |
+----+------------+---------+
| 7 | 2010-07-31 | 1218.30 |
+----+------------+---------+
| 8 | 2010-08-31 | 1207.96 |
+----+------------+---------+
| 9 | 2010-09-30 | 1272.12 |
+----+------------+---------+
| 10 | 2010-10-31 | 1280.27 |
+----+------------+---------+
| 11 | 2010-11-30 | 1275.60 |
+----+------------+---------+
| 12 | 2010-12-31 | 1346.45 |
+----+------------+---------+
| 13 | 2011-01-31 | 1337.07 |
+----+------------+---------+
| 14 | 2011-02-28 | 1338.37 |
+----+------------+---------+
| 15 | 2011-03-31 | 1349.14 |
+----+------------+---------+
And here's an example of what I'm trying to achieve:
tim's current bench value today = the sum of: (first(benchmark.value)/latest(benchmark.value))for every month from the first to the latest month inclusive First date = 2010-03 which is id 3 = 1264.91 Latest date = 2011-03 which is id 15 = 1349.14 (this is always the last row as I am trying to calculate on "today" and nothing in the future)
1/(first/latest) =1/(1264.91/1349.14) = 1.0666 [this is bench.id=3]
...now iterate...
1/(next/latest) =1/(1263.43/1349.14) = 1.0678 [bench.id=4]
1/(next/latest) =1/(1211.36/1349.14) = 1.1137 [bench.id=5]
1/(next/latest) =1/(1187.32/1349.14) = 1.1363 [bench.id=6]
1/(next/latest) =1/(1218.30/1349.14) = 1.1074 [bench.id=7]
1/(next/latest) =1/(1207.96/1349.14) = 1.1169 [bench.id=8]
1/(next/latest) =1/(1272.12/1349.14) = 1.0605 [bench.id=9]
1/(next/latest) =1/(1280.27/1349.14) = 1.0538 [bench.id=10]
1/(next/latest) =1/(1275.60/1349.14) = 1.0577 [bench.id=11]
1/(next/latest) =1/(1346.45/1349.14) = 1.0020 [bench.id=12]
1/(next/latest) =1/(1337.07/1349.14) = 1.0090 [bench.id=13]
1/(next/latest) =1/(1338.37/1349.14) = 1.0080 [bench.id=14]
...and finish up...
1/(current/latest) =1/(1349.14/1349.14) = 1.0000 [bench.id=15]
Total = 13.7997 = 1.0666 + 1.0678 + 1.1137 + 1.1363 + 1.1074 + 1.1169 + 1.0605 + 1.0538 + 1.0577 + 1.002 + 1.009 + 1.008 + 1
So I would then want to write that result back to Table A, giving me:
Table A (user)
+----+----------+------------+-------------+
| id | username | start-date | bench-value |
+----+----------+------------+-------------+
| 1 | tim | 2010-03-04 | 13.7997 |
+----+----------+------------+-------------+
As this is an iterative process it would be a much shorter calculation for a user like 'bob' who started in 2011-01.
I would also like to be able to do this every 4 months to produce termly stats so that someone like user tim would be calculated like this (the initial search to find the first date would need to take into account over a 4 month period):
1/(first/latest) = 2010-03 = 1/(1264.91/1349.14) = 1.0666
1/(next/latest) = 2010-07 = 1/(1218.30/1349.14) = 1.1074
1/(next/latest) = 2010-11 = 1/(1275.60/1349.14) = 1.0577
1/(current/latest) = 2011-03 = 1/(1349.14/1349.14) = 1.0000
Total = 1.0666 + 1.1074 + 1.0577 + 1 = 4.2317
That major issues I'm having are two fold: 1. how to use the user.start-date value for each user to pick the first(benchmark.value) based ont he year and the month (day is unimportant). 2. how to iteratively calculate the formula up to and including the latest value in the bench table - at the end of april, a new row with id=16 would be added and if this were run then the April value would become the last value used in the calculation.
As I'm learning SQL And PHP right now I'm not sure which parts of this process should be done in SQL and which in PHP.
Any and all help would be greatly appreciated as I'm determined to figure this out.
That major issues I'm having are two fold:
- how to use the user.start-date value for each user to pick the first(benchmark.value) based on the year and the month (day is unimportant).
- how to iteratively calculate the formula up to and including the latest value in the bench table - at the end of april, a new row with id=16 would be added and if this were run then the April value would become the last value used in the calculation.
As I'm learning SQL And PHP right now I'm not sure which parts of this process should be done in SQL and which in PHP.
Any and all help would be greatly appreciated as I'm determined to figure this out.
Just for reference, I've been reading:
- http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
- http://www.databasejournal.com/features/mssql/article.php/10894_2191631_3/Working-with-SQL-Server-DateTime-Variables.htm
- There were more but it won't let me post the other links yet...
There's almost too much info out there, so some guided advice would be realy appreciated. Thanks again.
