Analytic Functions
PERCENT_RANK ():
PERCENT_RANK to evaluate the relative
standing of a value within a query result set or partition. The first row
in any set has a PERCENT_RANK of 0. NULL values are included by default and are
treated as the lowest possible values.
Syntax:
PERCENT_RANK ()OVER
( [
partition_by_clause ] order_by_clause )
Example:
SELECT Ename, Salary, PERCENT_RANK() OVER (ORDER BY salary ASC) AS PercentRank FROM tblEmployee
Note: PERCENT_RANK() =
Total no of rows-1/position of value-1
Output
CUME_DIST():
Calculates the cumulative
distribution of a value in a group of values in SQL Server 2012. That is,
CUME_DIST computes the relative position of a specified value in a group of
values.
Syntax:
CUME_DIST ()OVER
( [
partition_by_clause ] order_by_clause )
Example:
SELECT Ename, Salary, CUME_DIST() OVER (ORDER BY salary Asc) AS CUMEDIST FROM tblEmployee
Note: CUME_DIST() = Total no of rows/Position of value
LEAD():
Accesses data from a subsequent row in
the same result set without the use of a self-join in SQL Server 2012. LEAD
provides access to a row at a given physical offset that follows the current
row. Use this analytic function in a SELECT statement to compare values in the
current row with values in a following row.
Syntax:
LEAD(
scalar_expression [
,offset ] , [
default ] )
OVER (
[ partition_by_clause ] )
Examples
SELECTProductId, YEAR([SalesDate]) AS SalesYear, SalesAmount AS CurrentAmount,
LEAD(SalesAmount, 1,0) OVER (ORDER BY YEAR(SalesDate)) AS NextAmount
FROM tblProduct
WHERE ProductId = 100
Output:
ProductId SalesYear CurrentAmount
NextAmount
----------------------------------------------------------
100 2010 1000.00 1500.00
100 2010 1500.00 3000.00
100 2011 3000.00 5400.00
100 2011 5400.00 300.00
100 2011 300.00 0.00
100 2010 1000.00 1500.00
100 2010 1500.00 3000.00
100 2011 3000.00 5400.00
100 2011 5400.00 300.00
100 2011 300.00 0.00
FIRST_VALUE():
Returns the first value in an
ordered set of values in SQL Server 2012.
Syntax:
FIRST_VALUE( [scalar_expression )
OVER ( [ partition_by_clause
]order_by_clause [ rows_range_clause ]
)
Example:
SELECTEname, Salary, FIRST_VALUE(Ename) over(order by salary Asc) AS Salary FROM tblEmployee
Output
Date & Time Functions
EOMONTH():
EOMONTH() returns the last day of the month that contains the specified date, with an optional offset.
Syntax:
EOMONTH( start_date [, month_to_add ] )
Examples:
DECLARE @date DATETIME
SET @date = '1/2/2012'
SELECT EOMONTH ( @date ) AS Result
GO
DECLARE @date VARCHAR(255)
SET @date = GETDATE()
SELECT EOMONTH ( @date ) AS Result
GO
DECLARE @date DATETIME
SET @date = GETDATE()
SELECT EOMONTH ( @date ) AS 'EndofCurrentMonthDate'
SELECT EOMONTH ( @date, 1 ) AS 'EndofNextMonthDate'
SELECT EOMONTH ( @date, -1 ) AS 'EndofLastMonthDate'
GO
Output:
EOMONTH() returns the last day of the month that contains the specified date, with an optional offset.
Syntax:
EOMONTH( start_date [, month_to_add ] )
Examples:
DECLARE @date DATETIME
SET @date = '1/2/2012'
SELECT EOMONTH ( @date ) AS Result
GO
DECLARE @date VARCHAR(255)
SET @date = GETDATE()
SELECT EOMONTH ( @date ) AS Result
GO
DECLARE @date DATETIME
SET @date = GETDATE()
SELECT EOMONTH ( @date ) AS 'EndofCurrentMonthDate'
SELECT EOMONTH ( @date, 1 ) AS 'EndofNextMonthDate'
SELECT EOMONTH ( @date, -1 ) AS 'EndofLastMonthDate'
GO
Output:
Logical Functions
IIF()
Returns one of two values, depending on whether the Boolean expression evaluates to true or false.
Syntax:
IIF ( boolean_expression, true_value, false_value )
DECLARE @a INT = 100;
DECLARE @b INT = 400;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;
Result:
TRUE
CHOOSE()
Returns the item at the specified index from a list of values.
Syntax:
CHOOSE ( index, val_1, val_2 [, val_n ] )
Example:
SELECT CHOOSE ( 2,1,3,4,6,8,10)
Output
3
String Functions
CONCAT ():
Returns a string that is the result of
concatenating two or more string values
Syntax:
CONCAT( string_value1, string_value2 [, string_valueN ] )
Example:
SELECT CONCAT ( 'Ram', 'chandran', 16 ) AS Result;
Output:
Ramachandran 16
