–table
DECLARE @CheckRegister TABLE
(
AccountNumber Varchar(10),
CheckNumber Varchar(10)
)
–data
INSERT INTO @CheckRegister VALUES (‘A1002’,101);
INSERT INTO @CheckRegister VALUES (‘A1010’,101);
INSERT INTO @CheckRegister VALUES (‘A1002’,102);
INSERT INTO @CheckRegister VALUES (‘A1010’,102);
INSERT INTO @CheckRegister VALUES (‘A1010’,103);
INSERT INTO @CheckRegister VALUES (‘A1002’,104);
INSERT INTO @CheckRegister VALUES (‘A1010’,104);
INSERT INTO @CheckRegister VALUES (‘A1002’,105);
INSERT INTO @CheckRegister VALUES (‘A1010’,105);
INSERT INTO @CheckRegister VALUES (‘A1010’,106);
INSERT INTO @CheckRegister VALUES (‘A1010’,107);
INSERT INTO @CheckRegister VALUES (‘A1002’,108);
INSERT INTO @CheckRegister VALUES (‘A1010’,108);
INSERT INTO @CheckRegister VALUES (‘A1002’,109);
INSERT INTO @CheckRegister VALUES (‘A1010’,109);
INSERT INTO @CheckRegister VALUES (‘A1002’,110);
INSERT INTO @CheckRegister VALUES (‘A1002’,111);
INSERT INTO @CheckRegister VALUES (‘A1002’,112);
INSERT INTO @CheckRegister VALUES (‘A1010’,112);
INSERT INTO @CheckRegister VALUES (‘A1002’,113);
INSERT INTO @CheckRegister VALUES (‘A1010’,113);
INSERT INTO @CheckRegister VALUES (‘A1010’,114);
INSERT INTO @CheckRegister VALUES (‘A1002’,115);
INSERT INTO @CheckRegister VALUES (‘A1002’,116);
INSERT INTO @CheckRegister VALUES (‘A1010’,116);
INSERT INTO @CheckRegister VALUES (‘A1010’,117);
INSERT INTO @CheckRegister VALUES (‘A1002’,118);
INSERT INTO @CheckRegister VALUES (‘A1010’,118);
INSERT INTO @CheckRegister VALUES (‘A1002’,119);
INSERT INTO @CheckRegister VALUES (‘A1010’,119);
INSERT INTO @CheckRegister VALUES (‘A1002’,120);
INSERT INTO @CheckRegister VALUES (‘A1010’,120);
INSERT INTO @CheckRegister VALUES (‘A1010’,179024);
INSERT INTO @CheckRegister VALUES (‘A1010’,179025);
INSERT INTO @CheckRegister VALUES (‘A1010’,179028);
–answer
WITH CTE_CheckRegister (AccountNumber, CheckNumber, NextCheckNumber)
AS
(
SELECT AccountNumber,
CheckNumber,
Lead(CheckNumber, 1) OVER (PARTITION BY AccountNumber ORDER BY CheckNumber) NextCheckNumber
FROM @CheckRegister
)
SELECT AccountNumber,
CASE
WHEN NextCheckNumber is NULL THEN CheckNumber
WHEN (CheckNumber = NextCheckNumber – 1) THEN CheckNumber
ELSE CheckNumber + ‘***’
END as CheckNumber
FROM CTE_CheckRegister
ORDER BY AccountNumber, CheckNumber