Find difference pattern of the given sequence.

This one is from the interview question I have asked. A sequence was given to me and asked to write a SQL query to find the difference pattern of the sequence and complete the sequence by adding missing numbers.
Give sequence:  '1,5,13,21,25,29,41'
Below is the query to find the answer:



SET NOCOUNT ON

 DECLARE @temp AS TABLE (Id INT IDENTITY, Nums INT, NextNum INT DEFAULT 0,
 Pattern AS (NextNum - Nums)
 )

 INSERT @temp (Nums)
 SELECT * FROM dbo.Split('1,5,13,21,25,29,41', ',')

-- Here assume that Split function you have added already OR please follow the link 
-- http://anishrana.blogspot.in/2012/03/split-string-in-sql-server-using-xml.html
-- and create one.

 UPDATE a
       SET NextNum = b.Nums
 FROM @temp a
 JOIN @temp b ON a.Id = (b.Id - 1)

 DECLARE @pattern INT = NULL,
              @firstNum INT = NULL,
              @lastNum INT = NULL

 SELECT TOP 1 @pattern = Pattern FROM @temp a
 GROUP BY Pattern
 ORDER BY COUNT(1) DESC

 SELECT @firstNum = MIN(Nums), @lastNum = MAX(Nums) FROM @temp

 --SELECT @pattern, @firstNum, @lastNum

WHILE (@lastNum >= @firstNum)
BEGIN
       PRINT @firstNum
       SET @firstNum += @pattern
END

PRINT 'Sequence generated with difference pattern of: ' + CONVERT(NVARCHAR(10), @pattern)

SET NOCOUNT OFF

Copy and run the script in SQL.

Below is the result:
--------------------------------------------------------------------
1
5
9
13
17
21
25
29
33
37
41
Sequence generated with difference pattern of: 4

Comments

Popular posts from this blog

Get Query String Values With Javascript

Change Css Class of li in ul onclick

Change Text Color with Javascript