Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server windowing functions


XLeratorDB / windowing

Use XLeratorDB / windowing for a wide variety of windowing analytical calculations. The feature-rich XLeratorDB function library lets you include calculations in any T-SQL statement including SELECT, INSERT, UPDATE, DELETE, CREATE VIEW as well as in CTEs, stored procedures, user-defined functions, and computed columns.

The XLeratorDB / windowing functions provide windowing features to SQL Server 2005 and later that are not otherwise available in all versions of SQL Server.

FUNCTION REFERENCE - WINDOWING FUNCTIONS
ANALYTIC FUNCTIONS
LAG
 
Obtain access to multiple rows within a resultant table, without the need for a self-join.
 
 
RUNNING VALUES
RunningCOUNT
 
Show how many rows are included in an ordered resultant table or within a partition in the resultant table, without having to do a self-join.
 
RunningSUM
 
Calculate the sum of column values in an ordered resultant table, without the need for a self-join.
 
RunningAVG
 
Calculate the average of column values in an ordered resultant table, without having to do a self-join.
 
RunningMAX
 
Calculate the maximum of column values in an ordered resultant table, without the need for a self-join.
 
RunningMIN
 
Calculate the minimum of column values in an ordered resultant table, without the need for a self-join.
 
RunningDEVSQ
 
Calculate the sum of squares of deviations of column values from their sample mean in an ordered resultant table, without the need for a self-join.
 
RunningSTDEV
 
Calculate the sample standard deviation of column values in an ordered resultant table, without the need for a self-join.
 
RunningSTDEVP
 
Calculate the population standard deviation of column values in an ordered resultant table, without the need for a self-join.
 
RunningVAR
 
Calculate the sample variance of column values in an ordered resultant table, without the need for a self-join.
 
RunningVARP
 
Calculate the population variance of column values in an ordered resultant table, without the need for a self-join.
 
RunningCORREL
 
Calculate the Pearson product moment correlation coefficient through data points in y- and x-values within a resultant table or partition, without the need for a self-join.
 
RunningCOVAR
 
Calculate the covariance through data points in y- and x-values within a resultant table or partition, without the need for a self-join.
 
RunningFORECAST
 
Calculate the predicted value of y for a specific value of x for a series of x- and y-values within a resultant table or partition, without the need for a self-join.
 
RunningINTERCEPT
 
Calculate the intercept of a series of x- and y-values within a resultant table or partition, without the need for a self-join.
 
RunningKURT_S
 
Calculate the sample kurtosis of column values in an ordered resultant table, without the need for a self-join.
 
RunningKURT_P
 
Calculate the population kurtosis of column values in an ordered resultant table, without the need for a self-join.
 
RunningPRODUCT *!*
 
Calculate the product of column values in an ordered resultant table, without the need for a self-join.
 
RunningRSQ
 
Calculate the square of the Pearson product moment correlation coefficient through data points in y- and x-values within a resultant table or partition, without the need for a self-join.
 
RunningSKEW_S
 
Calculate the samples skewness of column values in an ordered resultant table, without the need for a self-join.
 
RunningSKEW_P
 
Calculate the population skewness of column values in an ordered resultant table, without the need for a self-join.
 
RunningSLOPE
 
Calculate the slope of a series of x- and y-values within a resultant table or partition, without the need for a self-join.
 
RunningSTEYX
 
Calculate the standard error of the predicted y-value for each x in a regression within a resultant table or partition, without the need for a self-join.
 
RunningTTEST
 
Calculate the Student’s t-Test of column values in an ordered resultant table, without the need for a self-join.
 
RunningVOLATILITY *!*
 
Calculate the historical volatility based upon price or valuation data from column values in an ordered resultant table, without the need for a self-join.
 
 
MOVING VALUES
MovingCOUNT
 
Show how many rows are included in an ordered resultant table or within a partition in the resultant table, without having to do a self-join.
 
MovingSUM
 
Calculate the sum of column values in an ordered resultant table, without the need for a self-join.
 
MovingAVG
 
Calculate the moving average of column values in an ordered resultant table, without having to do a self-join.
 
MovingMAX
 
Calculate the maximum of column values in an ordered resultant table, without the need for a self-join.
 
MovingMIN
 
Calculate the minimum of column values in an ordered resultant table, without the need for a self-join.
 
MovingPRODUCT *!*
 
Calculate the product of column values in an ordered resultant table, without the need for a self-join.
 
MovingDEVSQ
 
Calculate the sum of squares of deviations of column values from their sample mean in an ordered resultant table, without the need for a self-join.
 
MovingSTDEV
 
Calculate the sample standard deviation of column values in an ordered resultant table, without the need for a self-join.
 
MovingSTDEVP
 
Calculate the population standard deviation of column values in an ordered resultant table, without the need for a self-join.
 
MovingVAR
 
Calculate the sample variance of column values in an ordered resultant table, without the need for a self-join.
 
MovingVARP
 
Calculate the population variance of column values in an ordered resultant table, without the need for a self-join.
 
MovingCORREL
 
Calculate the Pearson product moment correlation coefficient through data points in y- and x-values within a resultant table or partition, without the need for a self-join.
 
MovingCOVAR
 
Calculate the covariance through data points in y- and x-values within a resultant table or partition, without the need for a self-join.
 
MovingFORECAST
 
Calculate the predicted value of y for a specific value of x for a series of x- and y-values within a resultant table or partition, without the need for a self-join.
 
MovingINTERCEPT
 
Calculate the intercept of a series of x- and y-values within a resultant table or partition, without the need for a self-join.
 
MovingKURT_S
 
Calculate the sample kurtosis of column values in an ordered resultant table, without the need for a self-join.
 
MovingKURT_P
 
Calculate the population kurtosis of column values in an ordered resultant table, without the need for a self-join.
 
MovingRSQ
 
Calculate the square of the Pearson product moment correlation coefficient through data points in y- and x-values within a resultant table or partition, without the need for a self-join.
 
MovingSKEW_S
 
Calculate the sample skewness of column values in an ordered resultant table, without the need for a self-join.
 
MovingSKEW_P
 
Calculate the population skewness of column values in an ordered resultant table, without the need for a self-join.
 
MovingSLOPE
 
Calculate the slope of a series of x- and y-values within a resultant table or partition, without the need for a self-join.
 
MovingSTEYX
 
Calculate the standard error of the predicted y-value for each x in a regression within a resultant table or partition, without the need for a self-join.
 
MovingTTEST
 
Calculate the Student’s t-Test of column values in an ordered resultant table, without the need for a self-join.
 
MovingVOLATILITY *!*
 
Calculate the historical volatility based upon price or valuation data from column values in an ordered resultant table, without the need for a self-join.
 
 
MOVING AVERAGES
DEMA
 
Calculate the daily exponential weighted moving average of column values in an ordered resultant table, without the need for a self-join.
 
DWMA
 
Calculate a daily weighted moving average across multiple rows within a resultant table, without the need for a self-join.
 
RunningEWMA
 
Calculate the exponentially weighted moving average of column values in an ordered resultant table, without the need for a self-join.
 
MovingEWMA
 
Calculate the exponentially weighted moving average of column values in an ordered resultant table, without the need for a self-join.
 
 
INVENTORY CALCULATIONS
FIFO
 
Calculate FIFO (first in, first out) values in an ordered resultant table.
 
LIFO
 
Calculate LIFO (last in, first out) values in an ordered resultant table.
 
WAC
 
Calculate running weighted average cost in an ordered resultant table.
 
 
CAPM - CAPITAL ASSET PRICING MODEL
RunningSHARPE
 
Calculate the Sharpe ratio from column values in an ordered resultant table without the need to a self-join.
 
RunningINFORATIO
 
Calculate the information ratio from column values in an ordered resultant table without the need of a self-join.
 
MovingSHARPE
 
Calculate the Sharpe ratio from column values in an ordered resultant table without the need to a self-join.
 
MovingINFORATIO
 
Calculate the information ratio from column values in an ordered resultant table without the need for a self-join.
 
 
OTHER
XLDB_WINDOWING_VERSION
 
Return XLeratorDB / windowing version information.
 
 


*!*Added in most recent release of package

 



Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service