/********************************** Alteryx Weekly Challenge 406 ********************************/
-- declare all variables needed for data transformation and loop
-- NOTE - this could be wrapped in STORED PROCEDURE with @number variable as input parameter
DECLARE 
@number int, 
@iteration int,
@number_str varchar(10),
@number_asc int,
@number_desc int,
@magical_number int
SET @magical_number = 6174 -- this is magical number defined in challenge (remains unchanged)
SET @number = 1990 -- this number is changed to get the input (changed by user to get different results)
SET @iteration = 0 -- this number will be increasing with every loop round (changed dynamically by loop)
-- begin the loop which runs until the @number variable matches @magical_number when it returns number of iterations made
WHILE @number <> @magical_number
BEGIN
-- initiate iteration increase
SET @iteration = @iteration + 1
-- stringify the number
SET @number_str = SUBSTRING(CAST(@number AS varchar(10)), 1, 1)+',' + SUBSTRING(CAST(@number AS varchar(10)), 2, 1)+',' + SUBSTRING(CAST(@number AS varchar(10)), 3, 1)+',' + SUBSTRING(CAST(@number AS varchar(10)), 4, 1) 
-- create the highest number and convert back to integer
SET @number_desc =
(SELECT CAST(CONCAT(
	(SELECT TOP 1
	value AS number
	FROM string_split(@number_str,',')
	ORDER BY number DESC)
	,
	(SELECT 
	value AS number
	FROM string_split(@number_str,',')
	ORDER BY number DESC
	OFFSET 1 ROW
	FETCH NEXT 1 ROW ONLY)
	,
	(SELECT 
	value AS number
	FROM string_split(@number_str,',')
	ORDER BY number DESC
	OFFSET 2 ROW
	FETCH NEXT 1 ROW ONLY)
	,
	(SELECT 
	value AS number
	FROM string_split(@number_str,',')
	ORDER BY number DESC
	OFFSET 3 ROW
	FETCH NEXT 1 ROW ONLY)
	) AS int))
-- create the lowest number and convert back to integer
SET @number_asc =
(SELECT CAST(CONCAT(
	(SELECT TOP 1
	value AS number
	FROM string_split(@number_str,',')
	ORDER BY number)
	,
	(SELECT 
	value AS number
	FROM string_split(@number_str,',')
	ORDER BY number
	OFFSET 1 ROW
	FETCH NEXT 1 ROW ONLY)
	,
	(SELECT 
	value AS number
	FROM string_split(@number_str,',')
	ORDER BY number
	OFFSET 2 ROW
	FETCH NEXT 1 ROW ONLY)
	,
	(SELECT 
	value AS number
	FROM string_split(@number_str,',')
	ORDER BY number
	OFFSET 3 ROW
	FETCH NEXT 1 ROW ONLY)
	) AS int))
-- calculate resul number after this iteration round
SET @number = @number_desc - @number_asc
-- show the result number of each iteration in messages
PRINT(@number)
END
-- show final resulted number of iterations in SELECT statement
SELECT @iteration as NUMBER_OF_ITERATIONS