-- make some sample data

DROP TABLE #Sample

CREATE TABLE #Sample (Record INT, [Concatenate] CHAR(12))

INSERT INTO #Sample (Record, [Concatenate])

SELECT 1, '201201010510' UNION ALL

SELECT 2, '201201010511' UNION ALL

SELECT 3, '201201010514' UNION ALL

SELECT 4, '201201010515' UNION ALL

SELECT 5, '201201010517';

CREATE UNIQUE CLUSTERED INDEX ucx_Everything ON #Sample ([Concatenate], Record);

--------------------------

-- construct an inline tally table (Jeff Moden et al). This source of rows will be used

-- to fill in the missing rows in the data

;WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

iTally(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

--------------------------

-- Find the rows at the beginning and end of each gap

GapBounds AS (

SELECT

StartDate = CAST(s.[Concatenate] AS BIGINT),

EndDate = x.iConcatenate

FROM #Sample s -- CI scan

CROSS APPLY ( -- CI seek

SELECT TOP 1

iConcatenate = CAST(i.[Concatenate] AS BIGINT)

FROM #Sample i

WHERE i.[Concatenate] > s.[Concatenate]

ORDER BY i.[Concatenate]) x

WHERE x.iConcatenate - CAST(s.[Concatenate] AS BIGINT) > 1

)

---------------------------

-- Construct the missing rows

-- Start and End date from GapBounds tells us how many rows to take from iTally

-- Add 0-based sequence to Startdate to obtain the date for the row

SELECT CAST(StartDate + gaprows.rn AS CHAR(12))

FROM GapBounds g

CROSS APPLY (

SELECT TOP (EndDate+1-StartDate)

rn = ROW_NUMBER() OVER(ORDER BY StartDate)-1

FROM iTally

) gaprows