I prefer to use
Common Table Expressions[
^]:
DECLARE @tdata TABLE (grp INT, val INT)
DECLARE @dtmp TABLE (grp INT, val INT)
INSERT INTO @tdata (grp, val)
VALUES(301, 1)
INSERT INTO @tdata (grp, val)
VALUES(301, 2)
INSERT INTO @tdata (grp, val)
VALUES(301, 5)
INSERT INTO @tdata (grp, val)
VALUES(301, 7)
INSERT INTO @tdata (grp, val)
VALUES(301, 10)
INSERT INTO @tdata (grp, val)
VALUES(302, 2)
INSERT INTO @tdata (grp, val)
VALUES(303, 5)
INSERT INTO @tdata (grp, val)
VALUES(303, 6)
INSERT INTO @tdata (grp, val)
VALUES(303, 8)
INSERT INTO @tdata (grp, val)
VALUES(303, 9)
;WITH FullData AS
(
SELECT DISTINCT t1.grp, t1.val, 1 AS nval
FROM @tdata AS t1 LEFT JOIN (
SELECT grp, MAX(val) AS val
FROM @tdata
GROUP BY grp) AS t2 ON t1.grp=t2.grp
WHERE t1.val<=t2.val
UNION ALL
SELECT grp, val, nval + 1 AS nval
FROM FullData
WHERE nval < val
)
INSERT INTO @dtmp (grp, val)
SELECT DISTINCT t1.grp, t1.nval as val
FROM FullData AS t1 LEFT JOIN @tdata AS t2 ON t1.grp = t2.grp
SELECT DISTINCT grp, val
FROM @dtmp AS MissingVal
WHERE MissingVal.val NOT IN (SELECT val FROM @tdata WHERE grp = MissingVal.grp)
Returned values:
grp val
301 3
301 4
301 6
301 8
301 9
302 1
303 1
303 2
303 3
303 4
303 7
[EDIT #1]
If you want to check for missing values in a range 1-10 fro each group, replace the body of CTE function between brackets
(...)
with:
SELECT DISTINCT grp, val, 1 AS nval
FROM @tdata
WHERE val < 10
UNION ALL
SELECT grp, val, nval + 1 AS nval
FROM FullData
WHERE nval < 10
Returned values:
grp val
301 3
301 4
301 6
301 8
301 9
302 1
302 3
302 4
302 5
302 6
302 7
302 8
302 9
302 10
303 1
303 2
303 3
303 4
303 7
303 10
[/EDIT]