The winner of the competition stage in the respective age group is the participant who made the distance fastest. Each participant in the respective group receives for each stage points. The points obtained are calculated by dividing the time of the group winner by the time of the participant and multiplied by 1000. The total score is calculated by the sum of the points of the 5 best stages.

I have participant data like this.

AgeGroup   Start_Nr   First_name   Last_Name    Distance_result
-------------------------------------------------------------
M30          5         John        Lala           180
M35          1         Paul        Baba           175
M35          6         Patric      Ziza           192
M30          3         Peter       Mikel          190
S30          2         Sandra      Lilua          250
S30          4         Julia       Parker         260

And I want to calculate and display point like this

Age_Group|Start_Nr|First_name|Last_Name|Distance_result|Points
----------------------------------------------------------
M30         5         John        Lala           180          1000
M30         3         Peter       Mikel          190           947
M35         6         Patric      Ziza           175          1000
M35         1         Paul        Babas          185           946
S30         2         Sandra      Lilua          250          1000
S30         4         Julia       Parker         260           962

Each winner in age group gets 1000points, others in that age group get points calculating -> (MIN(Distance_result)/(Distance_result) * 1000)

SELECT  [Age_group],
    [Start_number] ,
    [First_name],
    [Last_name],
    [Stage_Nr],
    [Distance_result], (180/[Distance_result]*1000) AS Points,
    DENSE_RANK() OVER (PARTITION BY [Age_group] ORDER BY [Distance_result] ASC) AS PlaceRank
FROM [ParticipantDetails].[dbo].[ParticipantForm]

How can I use MIN(Distance_result) in each age group to do point calculations?
In my solution I can calculate points only inserting MIN(Distance_result) manually, but even then it's not correct to other age groups. In every age group there is a different best result.

CodePudding user response：

In my Example I have solved your problem with a subquery. I have also added round to remove the decimals and before that I have added cast to get some result, otherwise I would receive only 1 or 0, or you can do this: p1.[Distance_result] instead of cast...

SELECT  [AgeGroup],
        [Start_Nr] ,
        [First_name],
        [Last_name],
        [Distance_result], 
        (select round(min(p2.Distance_result)/p1.[Distance_result] * 1000, 0) AS Points 
         from ParticipantForm p2
         where p2.AgeGroup = p1.AgeGroup
         group by p2.AgeGroup) as Points, 
        DENSE_RANK() OVER (PARTITION BY [AgeGroup] ORDER BY [Distance_result] ASC) AS PlaceRank
FROM [ParticipantForm] p1

Here you can see the demo.

CodePudding user response：

You can use a sub-query to get the best times and join the table to it.
From a performance point of view it is preferable to have a sub-query in the join which is run only once than a row-level sub-query which is run for every line.
We avoid the need to cast as float and use round() by doing the multiplication by 1000 before the division.

    SELECT  
    [Age_group],
    [Start_number] ,
    [First_name],
    [Last_name],
    [Stage_Nr],
    [Distance_result], 
    (b.best*1000)/[Distance_result AS Points,
    DENSE_RANK() OVER (PARTITION BY [Age_group] ORDER BY [Distance_result] ASC) AS PlaceRank
    FROM [ParticipantDetails].[dbo].[ParticipantForm] 
    JOIN ( SELECT  [Age_group] AgeGroup,
                   MIN([Distance_result]) AS best 
           FROM [ParticipantDetails].[dbo].[ParticipantForm] 
           GROUP BY [Age_group]) AS b 
    ON b.[AgeGroup] = [ParticipantForm].[Age_group];

CodePudding user response：

Try calculating the minimum Distance_Result partitioned by Age_Group. Then calculate the points.

 WITH cte AS (
    SELECT * 
           , DENSE_RANK() OVER (PARTITION BY [Age_Group] ORDER BY [Distance_Result] ASC) AS Place_Rank
           , MIN(Distance_Result) OVER (PARTITION BY [Age_Group] ORDER BY [Distance_Result] ASC) AS Min_Distance
    FROM  [ParticipantForm]
 )
 SELECT [Age_group]
        , [First_Name]
        , [Last_Name]
        , [Start_Number]
        , [Distance_Result]
        , CAST(ROUND( [Min_Distance] * 1000.0 / [Distance_Result], 0 ) AS INT) AS Points
 FROM   cte

Results:

db<>fiddle here

CodePudding user response：

There are 8 stages in the competition, for each stage the participants receive points. I still need to calculate the total points when all the stages are over. The total score is calculated by the sum of the points of the 5 best stages.