Blog ala Vidar

SQL, AppFrame and other cool technologies

The OVER clause

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:

PersonID ProjectID TimesheetDate ManHours SumDailyManHours
Vidar Customer1 2010-12-01 1.00 6.00
Vidar Customer2 2010-12-01 5.00 6.00
Vidar Customer1 2010-12-02 1.00 7.50
Vidar Customer2 2010-12-02 1.00 7.50
Werner Customer1 2010-12-01 7.50 7.50
Werner Customer2 2010-12-02 7.50 7.50
Advertisements

Comments are closed.

%d bloggers like this: