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:
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.
-- 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
Below is the result:
--------------------------------------------------------------------
1
5
9
13
17
21
25
29
33
37
41
Sequence generated with difference pattern of: 4
Comments
Post a Comment