By Neil Putt

OVER Clause

In this article, we will discuss the OVER clause. It is used to add ranking functions and aggregate functions to any SELECT (even without a GROUP BY clause).

Note: it only applies to SQL Server 2005 and higher.

Syntax:
Ranking Window Functions
OVER ( [ PARTITION BY value_expression, … [ n ] ] <ORDER BY Clause> )

Aggregate Window Functions
OVER ( [ PARTITION BY value_expression, … [ n ] ] )

 

Arguments:
PARTITION BY- Divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

value_expression- Specifies the column by which the rowset produced by the corresponding FROM clause is partitioned. value_expression can only refer to columns made available by the FROM clause. value_expression cannot refer to expressions or aliases in the select list. value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.

<ORDER BY Clause>- Specifies the order to apply the ranking window function.

 

Example:
Using a GPMS Data Model, add the following fields to a Worksheet.

Chg Num
Chg Gross Amt
Pymt Amt
Pymt Post Date Act

Chg Num	Chg Gross Amt	Pymt Amt	Pymt Post Date Act
26485	103.00	10.00	04/11/2009
26485	103.00	41.00	03/28/2009
26485	103.00	10.00	04/28/2009

 

Add the following expressions and remove "Pymt Amt" and "Pymt Post Date Act".

Number Of Pymts:
COUNT([fact_pymt].[pymtamt]) OVER(PARTITION BY [fact_chg].[chgno])

First Pymt Date:
MIN([dim_date_pymt_postdt].[act_date]) OVER(PARTITION BY [fact_chg].[chgno])

Last Pymt Date:
MAX([dim_date_pymt_postdt].[act_date]) OVER(PARTITION BY [fact_chg].[chgno])

Chg Num	Chg Gross Amt	Number of Pymts	First Pymt Date	Last Pymt Date
26485	103.00	3	03/28/2009	04/28/2009
26485	103.00	3	03/28/2009	04/28/2009
26485	103.00	3	03/28/2009	04/28/2009

Then turn on Distinct Rows.

Chg Num	Chg Gross Amt	Number of Pymts	First Pymt Date	Last Pymt Date
26485	103.00	3	03/28/2009	04/28/2009