Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

Home


XLeratorDB/math

Use XLeratorDB/math for a wide variety of mathematical 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.

XLeratorDB/math is available in two packages - XLeratorDB/math which requires SQL Server 2005 or later, and XLeratorDB/math 2008 which requires SQL Server 2008 or later. 

Functions denoted with '2008+2012+2014 only' are only available in the XLeratorDB/math 2008 package and will not install or run  in SQL Server 2005. XLeratorDB 2008 packages contain functions which leverage SQL CLR features that are only available in SQL Server 2008 or later, namely 'multi-input aggregates' which provide a much easier calling syntax and typically run much faster than their scalar counterparts.

FUNCTION REFERENCE - MATH FUNCTIONS
ARITHMETIC/ALGEBRA
CEILING Ceiling to a multiple
COMBIN Combinatorial
EGCD Euclidean GCD
ELCM Euclidean LCM
EVEN Nearest Even
FACT Factorial
FACTDOUBLE Double Factorial
FLOOR Floor to a multiple
GCD Greatest Common Divisor
GREATEROF Return the comparative larger of two items based on the datatype of first item
INT Integer
LCM Least Common Multiple
LESSEROF Return the comparative smaller of two items based on the datatype of first item
MAX - 2008    new! Return the nth largest distinct value  2008+2012+2014 only
MIN - 2008    new! Return the nth smallest distinct value  2008+2012+2014 only
MROUND Round to a multiple
MULTINOMIAL Sum of squares  
MULTINOMIAL_q Sum of squares  
ODD Nearest Odd
PRODUCT Product of Values (similar to SUM)
PRODUCT38 Product of 2 decimal(38,18) values with precision 38 and scale 18
QUOTIENT Integer portion of a division
QUOTIENT38 Quotient of 2 decimal(38,18) values with precision 38 and scale 18
ROMAN Convert to Roman Numerals
ROUNDDOWN Round toward zero
ROUNDUP Round away from zero
SERIESSUM Sum of a Power Series  
SERIESSUM_q Sum of a Power Series  
SUMSQ Sum of the Squares
TRUNC Truncate to Integer
WAVG - 2008 Weighted average  2008+2012+2014 only
XL_LOG Log
   
TRIGONOMETRY
ACOSH Inverse Hyperbolic Cosine
ASINH Inverse Hyperbolic Sine
ATANH Inverse Hyperbolic Tangent
COSH Hyperbolic Cosine
CSC Cosecant
SEC Secant
SINH Hyperbolic Sine
TANH Hyperbolic Tangent
   
INTEGRALS
QUAD    new! Evaluate an infinite integral using 15-point Gauss-Kronrod Quadrature
QUADDE    new! Evaluate an infinite integral using Double Exponential Quadrature for non-Periodic functions
QUADGK    new! Evaluate a finite integral using 21-point Gauss-Kronrod Quadrature
QUADOSC    new! Evaluate an infinite integral using Double Exponential Quadrature for Periodic functions
QUADTS    new! Evaluate a finite integral using Tanh-Sinh method
   
DISTANCE
DISTANCE Calculate the distance between two points on earth
   
INTERPOLATIONS
BILINEARINTERP 2-dimensional interpolation  
INTERP Linear interpolation  
INTERP_q Linear interpolation  
INTERP - 2008 Linear interpolation  2008+2012+2014 only
INTERP2 - 2008 2-dimensional interpolation  2008+2012+2014 only
LNEAR Linear interpolation with extrapolation
LINEAR - 2008 Linear interpolation with extrapolation  2008+2012+2014 only
MONOSPLINE - 2008 Monotonic spline interpolation  2008+2012+2014 only
POLYCOEF - 2008 Polynomial coefficient  2008+2012+2014 only
POLYFIT Polynomial fitting  (table-valued function)  
POLYFIT_q Polynomial fitting  (table-valued function)  
POLYINTERP - 2008 Polynomial interpolation  2008+2012+2014 only
POLYRSQ - 2008 Polynomial goodness of fit  2008+2012+2014 only
POLYVAL - 2008 Polynomial value  2008+2012+2014 only
SPLINE Cubic spline interpolation  
SPLINE_q Cubic spline interpolation  
SPLINE - 2008 Cubic spline interpolation  2008+2012+2014 only
   
NUMBER/SERIES GENERATORS
RAND Returns a random float >=0 and <1
RANDBETWEEN Generate a Random integer between two values
RANDNORM Generate a Random integer based on the normal distribution
RANDBETA    new! Generate a sequence of random numbers from the beta distribution  (table-valued function)
RANDBINOM    new! Generate a sequence of random numbers from the binomial distribution  (table-valued function)
RANDCAUCHY    new! Generate a sequence of random numbers from the Cauchy distribution  (table-valued function)
RANDCHISQ    new! Generate a sequence of random numbers from the chi-squared distribution  (table-valued function)
RANDEXP    new! Generate a sequence of random numbers from the exponential distribution  (table-valued function)
RANDFDIST    new! Generate a sequence of random numbers from the F-distribution  (table-valued function)
RANDGAMMA    new! Generate a sequence of random numbers from the gamma distribution  (table-valued function)
RANDLAPLACE    new! Generate a sequence of random numbers from the LaPlace distribution  (table-valued function)
RANDLOGISTIC    new! Generate a sequence of random numbers from the logistic distribution  (table-valued function)
RANDNORMAL    new! Generate a sequence of random numbers from the normal distribution  (table-valued function)
RANDPOISSON    new! Generate a sequence of random numbers from the Poisson distribution  (table-valued function)
RANDSNORMAL    new! Generate a sequence of random numbers from the standard normal distribution  (table-valued function)
RANDTDIST    new! Generate a sequence of random numbers from the Student's t distribution  (table-valued function)
RANDWEIBULL    new! Generate a sequence of random numbers from the Weibull distribution  (table-valued function)
SeriesInt Generate a range of Integer values  (table-valued function)
SeriesFloat Generate a range of float values  (table-valued function)
SeriesDate Generate a range of date values  (table-valued function)
   
MATRIX FUNCTIONS
BKSUB Solve for the equation A*x=b for an upper-triangular matrix
DIAG Vector of the diagonal of the string representation of a matrix
EYE Generate an m-by-n identity matrix
FWDSUB Solve for the equation A*x=b for a lower-triangular matrix
LU Decompose a string representation of a matrix into the product of lower and upper triangular matrices
LUdecomp Decompose a de-normalized matrix into lower and upper  
LUdecomp_q Decompose a de-normalized matrix into lower and upper  
LUdecompN Decompose a normalized matrix into lower and upper  
LUdecompN_q Decompose a normalized matrix into lower and upper  
MATADD Add a value to every element of a matrix or add 2 matrices together
MATINVERSE Calculate the matrix inverse of a square (N x N) array
MATMULT Calculate the product of two matrices or multiply a matrix by a constant
MATRIX2STRING Turn table data into a string representation of a matrix  
MATRIX2STRING_q Turn table data into a string representation of a matrix  
MATSUB Subtract a value from every element of a matrix or subtract two matrices
MCOLS Return the number of columns in the string representation of a matrix
MCOLUMN Return a column from the string representation of a matrix
MCOV Calculate the covariance of a matrix  
MCOV_q Calculate the covariance of a matrix  
MCOVN Calculate the covariance of a matrix (normalized input)  
MCOVN_q Calculate the covariance of a matrix (normalized input)  
MDETERM Matrix Determinant  
MDETERM_q Matrix Determinant  
MDETERMN Matrix Determinant (normalized input)  
MDETERMN_q Matrix Determinant (normalized input)  
MINDEX Return the intersection of a row and a column from the string representation of a matrix
MMULT Matrix Multiplication  
MMULT_q Matrix Multiplication  
MMULTN Matrix Multiplication (normalized input)  
MMULTN_q Matrix Multiplication (normalized input)  
MINVERSE Matrix Inverse  
MINVERSE_q Matrix Inverse  
MINVERSEN Matrix Inverse (normalized input)  
MINVERSEN_q Matrix Inverse (normalized input)  
MRAND Generate a matrix of pseudo-random numbers
MRANDN Generate a matrix of pseudo-random numbers from the standard normal distribution
MROW Return a row from the string representation of a matrix
MROWS Return the number of rows in the string representation of a matrix
MTRACE Calculate the trace of a matrix  
MTRACE_q Calculate the trace of a matrix  
MTRACEN Calculate the trace of a matrix (normalized input)  
MTRACEN_q Calculate the trace of a matrix (normalized input)  
MTRIL Return the lower triangular part of the string representation of a matrix
MTRIU Return the upper triangular part of the string representation of a matrix
NMATRIX2STRING Turn third-normal form table data into a string representation of a matrix  
NMATRIX2STRING_q Turn third-normal form table data into a string representation of a matrix  
NMATRIX2STRING - 2008 Turn third-normal form table data into a string representation of a matrix  2008+2012+2014 only
ONES Generate an m-by-n matrix of ones
QR Decompose a string representation of a matrix into the product of an upper triangular matrix and an orthogonal matrix
QRdecomp Decompose a de-normalized matrix into the product of an upper triangular matrix and an orthogonal matrix  
QRdecomp_q Decompose a de-normalized matrix into the product of an upper triangular matrix and an orthogonal matrix  
TRANSPOSE Return the transposed matrix
ZERO Generate an m-by-n matrix of zeroes
   
MISC FUNCTIONS
XLDB_MATH_VERSION Version Information

This function cannot reference data from SQL Server 2014 memory-optimized tables


 |  View Topic History  |
Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service