|
|
Posted by Captain Paralytic on 04/03/07 12:10
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.
Navigation:
[Reply to this message]
|