(My)SQL- difference between two rows?

SiD the Turtle · 29 Dec 2007 at 14:12

I have a table that stores scores for each person each day eg:

id date score
1 date1 1000
2 date1 400
3 date1 700
1 date2 900
2 date2 400
3 date2 200
1 date3 800
2 date3 200
3 date3 100 etc.

What I want to find out is what the difference between each score for each person each day, so I end up with this:

id datea dateb diff
1 date1 date2 100
1 date2 date3 100
2 date1 date2 100
2 date2 date3 200
3 date1 date2 500
3 date2 date3 100

Any ideas on how to accomplish this? The limitation I have is that my host is using an older version of MySQl so I can't use limits in sub selects, can't create functions or views etc so it all has to be in one query. I was thinking of some kind of self join, but that brings back too many irrelevant rows (e.g. date 1 - date 3).

Trimbo · 29 Dec 2007 at 14:25

Sounds like you need a pivot table / query.

rpstewart · 29 Dec 2007 at 16:01

This is kind of off the top of my head so might not work...

Code:

select a.id, a.date, b.date, a.score-b.score
from <table> a, <table> b
where 
      a.id=b.id 
and a.date=b.date+1 day
and b.date is not null

You may need to tweak it to work on MySQL (I think in terms of DB2 SQL) so things like the join syntax might not be perfect, you may want to use Julian dates rather than date arithmetic. Also the SQL above won't count a person's first day, an outer join without the final where clause should fix that although you may need to case the subtraction as some RDBMS will calculate something minus null as null.

EDIT: Looking at it again the "and b.date is not null" is superfluous, b.date will always have a value.

SiD the Turtle · 29 Dec 2007 at 16:39

I think you're on to something there. Problem I have is that the dates are in fact date times, so even if I cast them into just dates I may run into join issues as some of the dates may end up the same. I do however have a table of the possible date time values that I could try to join to somehow.

Trimbo · 29 Dec 2007 at 17:56

You could join as (pseudocode) "where (dateb - datea) in hours is >= 24 and < 48"? Is that whan you intend?

SiD the Turtle · 29 Dec 2007 at 18:35

Almost.

At the moment the data is gathered once a day when I run a program. In future I want it to run much more frequently, say once every ten minutes. So what I really need is a method to work out the difference between each two adjacent runs of the program.

I'm sure they key is in having a separate date table to record the date/time of each run and I can think of how to do it if lets say it was an array in c#, I'm just struggling in SQL!

rpstewart · 29 Dec 2007 at 22:00

I'd suggest having an integer run ID column in the data as well and use that as part of the join in a similar piece of SQL to the one I posted earlier. The data insert program then reads the previous ID from another table, does all the inserts and then updates the ID table.