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

Change Css Class of li in ul onclick

Find missing sequence number. Get the numbers where sequence is missing.

Get Query String Values With Javascript