Calculating employee points

Question

I have one table on which I want to perform some calculations. The table stores employee points that are awarded for performing various tasks. The relevant structure is as follows (table has about 15,000 rows):

PointsTable:

  ID int IDENTITY(1, 1),
  EmployeeFirstName (varchar),
  EmployeeLastName (varchar),
  PointsAwarded (decimal(18, 2)),
  TransactionDate (date)

For each row, I want to return the following:

Employee Name
Points Awarded
Transaction Date
Employee's Running Total (cumulative sum for that one employee)
Employee's Total Points (sum of all points for that one employee)
Running Total (cumulative sum for all employees)
Total Points (sum of all points for all employees)
Employee's Running Percentage (Employee's Running Total / Employee's Total Points)
Employees' Total Percentage (Employee's Total Points / Total Points)
Total Running Percentage (Running Total / Total Points)

Here is the query I am currently using:

SELECT  EmployeeFirstName + ' ' + EmployeeLastName AS [Employee], 
        PointsAwarded, 
        TransactionDate, 
        [Employee Running Total], 
        [Employee Running Total] / [Employee Total Points] AS [Employee Running Percentage],
        [Employee Total Points],
        [Employee Total Points] / [Total Points] AS [Employee Total Percentage],
        [Running Total Points], 
        [Running Total Points] / [Total Points] AS [Total Running Percentage],
        [Total Points]
FROM 
    (
    SELECT  EmployeeFirstName, 
            EmployeeLastName, 
            PointsAwarded, 
            TransactionDate,
            SUM(PointsAwarded) OVER (PARTITION BY EmployeeFirstName, EmployeeLastName ORDER BY ID) AS [Employee Running Total],
            SUM(PointsAwarded) OVER (PARTITION BY EmployeeFirstName, EmployeeLastName) AS [Employee Total Points],
            SUM(PointsAwarded) OVER(ORDER BY ID) AS [Running Total Points],
            SUM(PointsAwarded) OVER() AS [Total Points]
    FROM PointsTable
    ) t

This query works and returns the results as expected. However, I feel like it can be optimized. Is it possible to include the calculations within the sub query, without repeating code?

For example, this code is necessary to give the Employee Running Percentage:

SUM(PointsAwarded) OVER (PARTITION BY EmployeeFirstName, EmployeeLastName ORDER BY ID) / 
SUM(PointsAwarded) OVER (PARTITION BY EmployeeFirstName, EmployeeLastName)

Is there a way to accomplish this with windowing - WITHOUT repeating the OVER functions a bunch of times? So far, the only way I've been able to achieve this is in the manner I've posted. I'm thinking a CTE would be more or less the same, but I'm not an expert.

Here is the execution plan.

Answer 1

That would be a fraction not a percentage

You should add an order by EmployeeFirstName, EmployeeLastName, ID