Posted by Christoph Burschka on 04/03/07 07:34

Tyrone Slothrop schrieb:
> I have a table which stores a member score on a monthly basis. I need
> to run a query which finds those members who have seen the greatest
> improvement and those who have shown the greatest loss from one month
> to the next.
>
> The fields:
> member_id (integer)
> score (double 3,1)
> date (date)
>
> I can find the greatest differences for the past two months with a
> query like:
>
> SELECT member_id, max(score) - min(score) as diff FROM monthly _report
> WHERE MONTH(CURDATE())=MONTH(Date)-1 OR
> MONTH[CURRDATE())=MONTH(Date)-2 GROOUP BY member_id;
>
> However, as I said before, I need to find those which show the
> greatest improvement and those who have shown the greatest loss
> between the two months.
>
> Is there are way of accomplishing this with a single query?
>
> TIA!

I see you are pulling this result only from a single unjoined table,
which greatly limits your options. For any tracking of changes, I'd
recommend you join the table with itself, using the current month and
the past month:

------

SELECT
old.member_id,
new.score-old.score AS diff,
FROM
monthly_report old
JOIN
monthly_report new
ON old.member_id=new.member_id
AND MONTH(new.Date)=MONTH(old.Date)+1
WHERE
MONTH(new.Date)=MONTH(CUR_DATE)
ORDER BY diff DESC
LIMIT 0,10;

(Top 10 winners)

-----

I can foresee a few special issues you'll have to work around:

- newly joined members (if you want to include them, giving them an
old score of 0) will require a LEFT OUTER JOIN and a temporary table.
But I'd
- In January, you'll have trouble with the jump from 12 to 1.
- I'm assuming you've been doing this for less than a year, or are
throwing out old records, because you'll be in trouble once you get
records with the same month and different years.

Sorting by diff [ASC] gives you the top losers, sorting by diff [DESC]
gives you the top winners. LIMIT 0,n gets only the first n members in
the ranking.

--CB

[Back to original message]