/*
=============================================================================================
CREATE DATE: 02/10/2010
 CREATED BY: SETH PHELABAUM
    PURPOSE: TSQL Challenge 20 - Identify repeating digits in Fibonacci Series
     ISSUES: 
      NOTES: 
Revision History
Date	 By		Change Made
-------- ---  	-------------------------------------
=============================================================================================
*/

WITH
	Fibo AS ( -- Use Recursive CTE To Generate Fibo Series
		SELECT 1 n, CAST(1 AS bigint) A, CAST(1 AS bigint) B
		UNION ALL
		SELECT n + 1, B, A+B
		FROM  Fibo
		WHERE n < 91),
	Dupes AS (
		SELECT A FiboNumber, 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%00%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%11%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%22%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%33%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%44%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%55%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%66%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%77%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%88%' THEN 1 ELSE 0 END + 
				CASE WHEN CAST(A AS varchar(20)) LIKE '%99%' THEN 1 ELSE 0 END D
		FROM Fibo),
	Final AS (SELECT FiboNumber, D, ROW_NUMBER() OVER (PARTITION BY D ORDER BY FiboNumber) RN FROM Dupes)

SELECT D NumRepeats, FiboNumber
FROM Final
WHERE D > 0 AND RN <6
ORDER BY D, RN

GO