DavidM

An earlier post was concerned with Prime and Perfect numbers ( Post) but this time they have asked for Combinations and Permutations

Just quickly the formulaes for them are:
Combinations = N!/(K!*(N-K)!)
Permutations = N!/(N-K)!)
where N is the number of elements and K is the size of the permutation/combination. and ! is for factorial...

First thing to do is to create the Factorial function...
--Factorial function
CREATE FUNCTION dbo.Factorial
(@N INT)
RETURNS BIGINT
AS
BEGIN
DECLARE @Result BIGINT
SET @Result = 1
WHILE @N > 1
BEGIN
  SET @Result = @Result * @N
  SET @N = @N-1
END
RETURN @Result
END
GO
SELECT dbo.Factorial(3)
GO
The maximum allowed value on N in this function is 20. Anything greater than this will cause an overflow.
I leave it up to someone else to use real/float data types for bigger numbers...

Once we have this, constructing the Combination and Permutation functions are trivial....
--Combinations
CREATE FUNCTION dbo.Combinations
(@N INT,
@K INT)
RETURNS BIGINT
AS
BEGIN
RETURN dbo.Factorial(@N)/(dbo.Factorial(@K)*dbo.Factorial(@N-@K))
END
GO
SELECT dbo.Combinations(10,4)
GO

--Permutations
CREATE FUNCTION dbo.Permutations
(@N INT,
@K INT)
RETURNS BIGINT
AS
BEGIN
RETURN dbo.Factorial(@N)/dbo.Factorial(@N-@K)
END
GO
SELECT dbo.Permutations(10,4)

As a side note you can also calculate all of these functions using the Numbers table via the wacky variable assignment method....
Below is the mind boggling code for Permutations of 10_P_4... I'm still trying to understand it myself!
DECLARE @Result BIGINT
DECLARE @N INT, @K INT
SELECT @N = 10, @K = 4
SET @Result = 1
SELECT @Result = @Result * Number, @Result = @Result / CASE WHEN (@N-@K) >= Number THEN @Result ELSE 1 END
FROM Numbers
WHERE Number BETWEEN 1 AND @N
SELECT @Result