MS SQL Server MSEQ - query problem

Author

query problem

2005-05-01, 11:25 am

Hi,

I have a problem to run a query like this scenario:

i have players in a table who have scored in many matches and i want to take
the 5 latest.

The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
finally 0.2.

playerid round score
1, 10, 122
1, 9, 123
1, 8, 222
1, 7, 333
1, 6, 222

And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2

Even handle if there are only 3 score.

Help, who can i do that in sql?

Regards
TW

Hugo Kornelis

2005-05-09, 8:24 pm

On Sun, 1 May 2005 17:05:50 +0200, tw wrote:

>Hi,
>
>I have a problem to run a query like this scenario:
>
>i have players in a table who have scored in many matches and i want to take
>the 5 latest.
>
>The latest i want to multiply with 1.0 and then after with 0.8, 0.6, 0.4 and
>finally 0.2.
>
>playerid round score
>1, 10, 122
>1, 9, 123
>1, 8, 222
>1, 7, 333
>1, 6, 222
>
>And i want to multiply 122 * 1 + 123*0,8 + 222*0,6 + 333*0,4 + 222*0,2
>
>Even handle if there are only 3 score.
>
>Help, who can i do that in sql?
>
>Regards
>TW
>

Hi TW,

The following is untested. Please see www.aspfaq.com/5006 to find out
what you should post to enable me to test my suggestions:

SELECT s.playerid, SUM (f.factor * s.score)
FROM ScoreTable AS s
INNER JOIN (SELECT 1, 1.0 UNION ALL
SELECT 2, 0.8 UNION ALL
SELECT 3, 0.6 UNION ALL
SELECT 4, 0.4 UNION ALL
SELECT 5, 0.2) AS f(round, factor)
ON round = (SELECT COUNT(*)
FROM ScoreTable AS s2
WHERE s2.playerid = s.playerid
AND s2.round >= s.round)
GROUP BY s.playerid

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links