Please always provide sample data. Also you have not show us how you would like the final result to look like. Here is one of dozens of ways to do it. scrapped this from online.
;WITH Data(Datum)
AS
(
select 3 union
select 6
),
CTE
AS
(
SELECT MIN(Datum) Start,
MAX(Datum) Finish
FROM Data
UNION ALL
SELECT Start + 1,
Finish
FROM CTE
WHERE Start < Finish
)
SELECT *
FROM CTE
WHERE NOT EXISTS
(
SELECT 1 FROM Data WHERE Data.Datum = cte.Start
)
another way.
declare @dbforever table(ID int, Name nvarchar(1500), SomeDate datetime )
insert into @dbforever(id, Name, SomeDate)
select 3, 'John', dateadd(dd,3,getdate()) union
select 6, 'Mary', dateadd(dd,6 ,getdate())
WITH SourceData AS
(
SELECT
ID AS ID,
RowNum = ROW_NUMBER() OVER (ORDER BY ID)
FROM @dbforever
),
Ranked AS
(
SELECT
*,
DENSE_RANK() OVER (ORDER BY ID - RowNum) As Series
FROM SourceData
),
Counted AS
(
SELECT
*,
COUNT(*) OVER (PARTITION BY Series) AS SCount
FROM Ranked
),
Gaps AS
(
SELECT
MinID = MIN(ID),
MaxID = MAX(ID),
Series
FROM
Counted
GROUP BY Series
)
SELECT
FirstNumberInGap = (a.MaxID + 1),
LastNumberInGap = (b.MinID - 1),
GapSize = ((b.MinID - 1) - a.MaxID)
FROM Gaps a
JOIN Gaps b ON a.Series + 1 = b.Series
ORDER BY FirstNumberInGap