2. PHPRunner General questions
  3. [SOLVED]  Calculated column from last 2 results
This topic is locked
[SOLVED]

 Calculated column from last 2 results

 		7/28/2013 9:38:09 PM
PHPRunner General questions
G
 gdude66 author

Hi

If i had 8 columns in data sheet view eg year 1, year 2, year 3, year 4, year 5, year 6, year 7, year 8 and then added another column called growth how could i get this column to calculate the growth from one year to another based on the last2 entries.

By last 2 entries i mean the last 2 results if they exist. So there may be data in year 1, year 2, year 3 for 1 user and I would want the calculated field to subtract year 2 from year 3. ANother user may have data in year 1, year 2, year 3, year 4, year5 so i would want the growth field to subtract year 4 from year 5. I have 50 users in one table with various progress across the fields.
Any ideas?
Sergey Kornilov admin 7/30/2013

While I don't have exact code for this task here is an article that features similar approach:

http://www.asprunner.com/forums/topic/21091-implementing-running-total-in-phprunner/
For each field you need to save two last values in session variables as you process the list page. You can use all the same events for this task.
N
 		nohope4you 7/30/2013

You could add in your sql query in phprunner:
SELECT

year1

year2

...

year8

//this is the new part

year1+year2+year3...+year8 as Growth
Additionally you may want to add fields that could be called lastyear1 lastyear2 and so on that hold the last filled in piece of data and add that to the mix in your sql query and have them hidden on your add pages so it would add those values in your query if the new value is null or 0



Hi

If i had 8 columns in data sheet view eg year 1, year 2, year 3, year 4, year 5, year 6, year 7, year 8 and then added another column called growth how could i get this column to calculate the growth from one year to another based on the last2 entries.

By last 2 entries i mean the last 2 results if they exist. So there may be data in year 1, year 2, year 3 for 1 user and I would want the calculated field to subtract year 2 from year 3. ANother user may have data in year 1, year 2, year 3, year 4, year5 so i would want the growth field to subtract year 4 from year 5. I have 50 users in one table with various progress across the fields.
Any ideas?
G
 		gdude66 author 8/5/2013

Got it using this code

getdif.growth

FROM ttstudent

INNER JOIN student ON ttstudent.studentid = student.code

INNER JOIN ondemand ON ttstudent.studentid = ondemand.Student_ID

LEFT JOIN (

SELECT si,ty,la.Student_ID laid,pr.Student_ID prid,(la.score-pr.score) growth FROM (

SELECT si,ty,max(test_date) cyprev, cylast FROM ondemand INNER JOIN (

SELECT Student_ID si,type ty,max(test_date) cylast FROM ondemand

GROUP BY Student_ID,type

) od ON si=Student_ID AND ty=type AND cylast>test_date

GROUP BY si,ty, cylast

) getlast2

INNER JOIN ondemand la ON la.Student_ID=si AND la.type=ty AND la.test_date=cylast

INNER JOIN ondemand pr ON pr.Student_ID=si AND pr.type=ty AND pr.test_date=cyprev

) getdif ON si=ondemand.Student_ID AND ty=ondemand.type

WHERE ondemand.type='Numeracy'

GROUP BY ondemand.Student_ID