Click here to Skip to main content
15,891,837 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I have a requirement like this

i have the data like

301 1
301 2
301 5
301 7
301 10
302 2
303 5
303 6
303 8
303 9


In this data i want find out missing number in each group.

For example in 301 group the missing numbers are 3,4,8,9(Assume that the range is 1 to 10)

in 303 group the missing numbers are 1,3,4,7,10


so now i want find missing numbers in sql server.
could any one help me .


Thanks

Prakash.ch
Posted

I prefer to use Common Table Expressions[^]:

SQL
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:
SQL
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]
 
Share this answer
 
v2
Comments
prakash.chakrala 14-May-13 7:48am    
In 302 group only one value is available i.e.,2
so in 302 the answer has to look like without 2 remaining all numbers from 1 to 10
Maciej Los 14-May-13 7:57am    
In my example query i check the max value for each group: 301, 302, 303.
Based on example data (from your question), missing value for
- 301 group is: 3, 4, 6, 8, 9,
- 302 group is: 1,
- 303 group is: 1, 2, 3, 4 ,7,

So, if you want to find missing values from 1 to 10, check my answer in a while...
prakash.chakrala 14-May-13 8:03am    
could u full fill the answer

means where i need to check the answer
Maciej Los 14-May-13 8:05am    
Check my answer now - [EDIT #1] section.
prakash.chakrala 14-May-13 8:12am    
k Thank you very much.
 
Share this answer
 
Comments
prakash.chakrala 14-May-13 7:33am    
This will not helpful to me.
prakash.chakrala 14-May-13 9:57am    
i have another requirement that

301 1 june
301 2 june
301 5 june
301 7 june
301 10 june

301 2 july
301 3 july
301 5 july
301 8 july
301 9 july

303 5 june
303 6 june
303 8 june
303 9 june

303 2 july
303 4 july
303 8 july
303 10 july


In this data i want find out missing number in each group.

For example in 301 and june group the missing numbers are 3,4,6,8,9(Assume that the range is 1 to 10)

in 301 and july group the missing numbers are 1,4,6,7,10


like that in all groups

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900