IF OBJECT_ID('dbo.NTallyFast','IF') IS NOT NULL DROP FUNCTION dbo.NTallyFast;
IF OBJECT_ID('samd.lcss8K','IF') IS NOT NULL DROP FUNCTION samd.lcss8K;
IF OBJECT_ID('samd.lcssRange8K','IF') IS NOT NULL DROP FUNCTION samd.lcssRange8K;
IF OBJECT_ID('dbo.O','IF') IS NOT NULL DROP FUNCTION dbo.O;
GO
CREATE FUNCTION dbo.O (@L BIGINT,@H BIGINT,@N BIGINT)
/*****************************************************************************************
[Purpose]:
To allow developers to perform a DESCending sort against a CTE tally table without a sort
in the execution plan (see below for more details). Given a set of natural numbers from
@L to @Hdbo.O(@L,@H,@N) returns the "finite opposite" of @N relative to the set.
Traditional opposite numbers (AKA additive inverse numbers) there are two numbers of
equal distance from 0; the higher number is a non-negative number and the lower number is
the higher number times -1. Ex: the opposite of -10 is 10, the opposite of 4 is -4 and
the opposite of 0 is 0. With traditional opposite numbers zero (0) represents the median
between each set of opposite numbers. For example, consider the set of numbers between
2 and -2: -2,-1,0,1,2... Here 0 is the median, consider 3 and -3: -3,-2,-1,0,1,2,3...
again the median is 0. The median between traditional opposites is always 0 because there
an infinite number of positive and negative integers. With this is mind - traditional
opposite numbers can be thought of as "infinite opposites".
A *Finite* opposite number exists when you define a low and high number. Consider a set
of sequential numbers beginning with 6 through 10: (6,7,8,9,10); here the median is 8 and
which means the "finite opposite" of 8 is 8, the finite opposite of 7 is 9 and the finite
opposite of 6 is 10. With finite opposites the highest number is in the set is always the
opposite of the lowest number, the second highest is the opposite of the second lowest,
etc.
[Author]:
Alan Burstein
[Compatibility]:
SQL Server 2005+
[Syntax]:
--===== Autonomous
SELECT O.Op
FROM dbo.O(@L,@H,@N) AS O;
--===== Against a table using APPLY
SELECT t.col1, t.col2, t.col3, O.O
FROM dbo.someTable AS t
CROSS APPLY dbo.O(t.col1,t.col2,t.col3) AS O;
[Parameters]:
@L = INT; The lower boundary of the set
@H = INT; The upper boundary of the set
@N = INT; The number to evaluate relative to the set beginning with @L & ending with @H
[Returns]:
inline table valued function returns:
Op = Int; The "Finite Opposite" of @N
[Dependencies]:
NA
[Developer Notes]:
When @L and @H are infinate opposites then the finite and infinate opposite values are
the same. The magic of the function is that there are no wrong numbers. @high does not
have to be lower than @low and @N does not have to be between @low and high.
1. Returns NULL when @L, @H or @N are NULL.
2. dbo.O is deterministic. For more deterministic functions see:
https://msdn.microsoft.com/en-us/library/ms178091.aspx
[Examples]:
--===== 1.
-- (1.1)
;
-- (1.2)
;
--===== 1.
-- (1.1)
;
[Revision History]:
-----------------------------------------------------------------------------------------
Rev 00 - 20180000 - Initial Development - Alan Burstein
Rev 01 - 20181221 - Initial Development - Alan Burstein
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT Op = @L+@H-@N;
GO