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.

• S3S: In regards to the CTE portion, it would only give you better readability (top-down versus inner-outer) and the optimizer should treat them the same. Can you paste the execution plan? brentozar.com/pastetheplan
• Stan Shaw: @scsimon I edited the question and added the execution plan - thank you for the link.
• S3S: I don't see anything glaring here brother--i'd say you are solid
• Stan Shaw: @scsimon Thanks for taking the time to review it. I use this approach pretty widely, but it just LOOKS bad, so I was hoping there was a slicker way of coding it. No performance issues, and no one seemed to object to it, so I'll continue using this approach. It's certainly easy! Thanks again.
• dnoeth: In Standard SQL there's a WINDOW definition for exactly this task, but most DBMSes (including SQL Server) don't implement it.

Answer 1

That would be a fraction not a percentage

You should add an order by EmployeeFirstName, EmployeeLastName, ID

• dnoeth: Why would you add the partitioning columns to the order columns?
• paparazzo: @dnoeth Because there is no sort without an order by. That would be a weird report if the user was not together and weird running total. He is just getting lucky that the optimizer using what was easy pretty.
• dnoeth: Now I see, it's the final order, of course.
• Stan Shaw: I'm sorry, @Paparazzi, but I don't understand what you're trying to say.
• paparazzo: What is not clear?