Solution to TSQL Beginners Challenge 3

--TSQL Beginners Challenge 3 - Find the Factorial 
DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact 
SELECT 0 UNION ALL 
SELECT 1 UNION ALL 
SELECT 3 UNION ALL 
SELECT 5 UNION ALL 
SELECT 10;

--SELECT * FROM @Fact;
--The solution:
with input as 
(select 
Nums as Nums, 
case when Nums>0 then Nums when Nums<=0 then 1 end as Fact from @Fact 
where Nums>=0 union all select Nums, Fact-1 from input where Fact>1)
select distinct Nums, POWER(10,SUM(LOG10(Fact))) as Factorial from input group by Nums;
--select Nums, (case when Nums>1 then Nums-1 else 1 end)*Nums from @Fact inner join input on input.Nums = Input

Tags: