Is This Possible?

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!

Re: Is This Possible?

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

Re: Is This Possible?

Christoph Burschka schrieb:
> 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

Sorry, forgot to finish the sentence:

But I'd recommend you get this query to work properly before adding a
more complex feature.

;)

Re: Is This Possible?

On 3 Apr, 08:34, Christoph Burschka 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.

Re: Is This Possible?

On 3 Apr 2007 05:10:52 -0700, "Captain Paralytic"
wrote:

>On 3 Apr, 08:34, Christoph Burschka >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!