3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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 \$\endgroup\$ – scsimon Jul 6 '17 at 15:23
  • \$\begingroup\$ @scsimon I edited the question and added the execution plan - thank you for the link. \$\endgroup\$ – Stan Shaw Jul 6 '17 at 16:49
  • \$\begingroup\$ I don't see anything glaring here brother--i'd say you are solid \$\endgroup\$ – scsimon Jul 7 '17 at 18:20
  • 1
    \$\begingroup\$ @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. \$\endgroup\$ – Stan Shaw Jul 7 '17 at 18:21
  • 1
    \$\begingroup\$ In Standard SQL there's a WINDOW definition for exactly this task, but most DBMSes (including SQL Server) don't implement it. \$\endgroup\$ – dnoeth Jul 8 '17 at 10:06
1
\$\begingroup\$

That would be a fraction not a percentage

You should add an order by EmployeeFirstName, EmployeeLastName, ID

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Why would you add the partitioning columns to the order columns? \$\endgroup\$ – dnoeth Jul 8 '17 at 18:43
  • \$\begingroup\$ @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. \$\endgroup\$ – paparazzo Jul 8 '17 at 19:28
  • \$\begingroup\$ Now I see, it's the final order, of course. \$\endgroup\$ – dnoeth Jul 8 '17 at 20:02
  • \$\begingroup\$ I'm sorry, @Paparazzi, but I don't understand what you're trying to say. \$\endgroup\$ – Stan Shaw Jul 10 '17 at 11:47
  • \$\begingroup\$ What is not clear? \$\endgroup\$ – paparazzo Jul 11 '17 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.