Find the Consecutive numbers of given string

For Consecutive number as per your required below SQL Query will work

CREATE TABLE PartNumbersAvailable
(
part_number INT NOT NULL PRIMARY KEY
)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (1)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (2)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (3)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (4)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (5)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (7)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (8)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (9)
GO
CREATE VIEW NoConsecutivePartNumbers (part_number)
AS
SELECT P1.part_number
FROM PartNumbersAvailable AS P1
LEFT OUTER JOIN
PartNumbersAvailable AS P2
ON P2.part_number = P1.part_number + 1
WHERE P2.part_number IS NULL
GO
CREATE VIEW PartNumberSequences
(first_consecutive, last_consecutive, sequence_length)
AS
SELECT MIN(part_number),
last_consecutive,
last_consecutive - MIN(part_number) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_number) AS last_consecutive
FROM PartNumbersAvailable AS P1
LEFT OUTER JOIN
NoConsecutivePartNumbers AS P2
ON P2.part_number >= P1.part_number
GROUP BY P1.part_number) AS P
GROUP BY last_consecutive
GO
SELECT DISTINCT STUFF((SELECT DISTINCT ', ' + CAST(t1.part_number AS VARCHAR)
from PartNumbersAvailable t1
where (t.[first_consecutive] <= t1.[part_number]
AND t.[last_consecutive] >= t1.[part_number])
GROUP BY t1.part_number
HAVING Count(*) <= t.sequence_length
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'') PartNumbers
FROM PartNumberSequences t
UNION ALL
SELECT 'Count: '+CAST(COUNT(*) AS VARCHAR) FROM PartNumberSequences
GO

This will display output as below

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain7y ago
Accepted answer
For Consecutive number as per your required below SQL Query will work
CREATE TABLE PartNumbersAvailable
(
part_number INT NOT NULL PRIMARY KEY
)

INSERT INTO PartNumbersAvailable (part_number)
VALUES (1)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (2)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (3)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (4)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (5)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (7)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (8)
INSERT INTO PartNumbersAvailable (part_number)
VALUES (9)
GO

CREATE VIEW NoConsecutivePartNumbers (part_number)
AS
SELECT P1.part_number
FROM PartNumbersAvailable AS P1
LEFT OUTER JOIN
PartNumbersAvailable AS P2
ON P2.part_number = P1.part_number + 1
WHERE P2.part_number IS NULL
GO

CREATE VIEW PartNumberSequences
(first_consecutive, last_consecutive, sequence_length)
AS
SELECT MIN(part_number),
last_consecutive,
last_consecutive - MIN(part_number) + 1
FROM (SELECT P1.part_number,
MIN(P2.part_number) AS last_consecutive
FROM PartNumbersAvailable AS P1
LEFT OUTER JOIN
NoConsecutivePartNumbers AS P2
ON P2.part_number >= P1.part_number
GROUP BY P1.part_number) AS P
GROUP BY last_consecutive
GO

SELECT DISTINCT STUFF((SELECT DISTINCT ', ' + CAST(t1.part_number AS VARCHAR)
         from PartNumbersAvailable t1
         where (t.[first_consecutive] <= t1.[part_number]
            AND t.[last_consecutive] >= t1.[part_number])
            GROUP BY t1.part_number
            HAVING Count(*) <= t.sequence_length
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,2,'') PartNumbers
FROM PartNumberSequences t
UNION ALL
SELECT 'Count: '+CAST(COUNT(*) AS VARCHAR) FROM PartNumberSequences
GO
This will display output as below
+1
Siddhartha Sharma7y ago
Do you need solution in c# or SQL ?
0