Blog ala Vidar

To use the ROW_NUMBER, RANK, DENSE_RANK or NTILE functions you need to use the OVER clause, but did you know you can use it on other functions as well? Like SUM, AVG, MAX etc. In the example under I want to list all timesheet records, but I want to know if the person has 7.5 hours each day, so I’ve made a “SumDailyManHours” column in the SELECT. This is easily done with SUM(ManHours) OVER (PARTITION BY PersonID, TimesheetDate). In the “old” days I’d do something like the second SELECT statement.

CREATE TABLE #Timesheet (
	PersonID NVARCHAR(8),
	ProjectID NVARCHAR(10),
	TimesheetDate DATE,
	ManHours DECIMAL(4, 2)
)

INSERT INTO #Timesheet
  (PersonID, ProjectID, TimesheetDate, ManHours)
VALUES
  ('Vidar', 'Customer1', '2010-12-01', 1),
  ('Vidar', 'Customer2', '2010-12-01', 5),
  ('Vidar', 'Customer1', '2010-12-02', 1),
  ('Vidar', 'Customer2', '2010-12-02', 6.5),
  ('Werner', 'Customer1', '2010-12-01', 7.5),
  ('Werner', 'Customer2', '2010-12-02', 7.5)

-- New way of doing it
SELECT
  *,
  SUM(ManHours) OVER (PARTITION BY PersonID, TimesheetDate) AS SumDailyManHours
	FROM #Timesheet

-- Old way of doing it
SELECT
  *,
  (SELECT SUM(ManHours)
		FROM #Timesheet
		WHERE PersonID = T.PersonID
		  AND TimesheetDate = T.TimesheetDate) AS SumDailyManHours
	FROM #Timesheet AS T

DROP TABLE #Timesheet

Here’s the result for both SELECT statements: