How to get missing number in Grouped data

Question

1.00/5 (2 votes)

See more:

Hi I have another requirement like this

i have the data like

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

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

Thanks

Prakash.ch

Posted 14-May-13 3:56am

prakash.chakrala

Updated 14-May-13 4:48am

Maciej Los

v2

Add a Solution

Comments

gvprabu 14-May-13 10:07am

Already U got Solution Without Month right... use same Solution. Why u raised new Question?

prakash.chakrala 14-May-13 10:10am

I asked with Month also. and could you read the question once again.

gvprabu 14-May-13 10:10am

Use same Solution...
http://www.codeproject.com/Questions/592749/Howplustoplusgetplusmissingplusnumberplusinplusplu
If u have any Clarification, pls contact Maciej Los

prakash.chakrala 14-May-13 10:13am

That solution will not give correct output.

gvprabu 14-May-13 10:20am

see u have to add one more column in that group. just understand that solution and change it. If any difficulties then ask the person who gave the solution.

Maciej Los 14-May-13 10:32am

Agree!
My virtual 5!

prakash.chakrala 14-May-13 10:27am

Already i have checked the solution but its not giving actual output.

gvprabu 14-May-13 10:37am

he (Maciej Los) gave the solution... check that solution and this one, U can understand the Query.. If any difficulties or which place u are not able to understand then ask. once u got the solution don't do COPY and PAST. Check the logic how its working? any new logic added or... like that. Then only u can improve ur skills. All the best.

prakash.chakrala 14-May-13 10:45am

I know the logic will be same it will not change.Few minutes back you told that same logic will be useful with adding the last condition how can we achieve the new requirement.Any way i got the solution.Thank you for being the part question.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-05-14T04:30:00

You need to do it in the same way as previous:

SQL

DECLARE @tdata TABLE (grp INT, val INT, mth NVARCHAR(30))
DECLARE @dtmp TABLE (grp INT, val INT, mth NVARCHAR(30))

INSERT INTO @tdata (grp, val, mth)
VALUES(301, 1, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 2, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 5, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 7, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 10, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 2, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 3, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 5, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 8, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(301, 9, 'july')
--INSERT INTO @tdata (grp, val, mth)
--VALUES(302, 2, 'june')
--INSERT INTO @tdata (grp, val, mth)
--VALUES(302, 2, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 5, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 6, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 8, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 9, 'june')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 2, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 4, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 8, 'july')
INSERT INTO @tdata (grp, val, mth)
VALUES(303, 10, 'july')


;WITH FullData AS
(
	SELECT grp, val, mth, 1 AS nval
	FROM @tdata
	WHERE val<=10 
	UNION ALL
	SELECT grp, val, mth, nval + 1 AS nval
	FROM FullData
	WHERE nval < 10
) 
INSERT INTO @dtmp (grp, val, mth)
SELECT DISTINCT t1.grp, t1.nval as val, t1.mth
FROM FullData AS t1 LEFT JOIN @tdata AS t2 ON t1.grp = t2.grp 

SELECT DISTINCT grp, val, mth
FROM @dtmp AS MissingVal
WHERE MissingVal.val NOT IN (SELECT val FROM @tdata WHERE grp = MissingVal.grp AND mth = MissingVal.mth)
ORDER BY grp, mth, val

results:

grp     val     mth
301	1	july
301	4	july
301	6	july
301	7	july
301	10	july
301	3	june
301	4	june
301	6	june
301	8	june
301	9	june
303	1	july
303	3	july
303	5	july
303	6	july
303	7	july
303	9	july
303	1	june
303	2	june
303	3	june
303	4	june
303	7	june
303	10	june