Posted by Tyrone Slothrop on 04/03/07 13:52

On 3 Apr 2007 05:10:52 -0700, "Captain Paralytic"
<paul_lautman@yahoo.com> wrote:

>On 3 Apr, 08:34, Christoph Burschka <christoph.bursc...@rwth-
>aachen.de> wrote:
>> 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- Hide quoted text -
>>
>> - Show quoted text -
>
>Using DATE_ADD() will take care of years and year boundaries.

Indeed!

Thanks for the assistance!

[Back to original message]