Missing Number Find out

Question

Post reply

Missing Number Find out

vs.satheesh

SSCrazy

Points: 2392
More actions
December 7, 2012 at 4:38 am

#393765

Hi
I am doing University Project.In my scenario I have two tables.One table name called studentmaster otherone examapplication. in following structure
studentMaster
RegisterNo Name
101 satheesh
102 xxxx
103 yyyy
104 ZZZZ
105 aaaa
106 bbbb
107 cccc
108 dddd
109 eeee
110 fffff
111 gggg
.
.
.
999 naras
Second table is called Examapplication,In this who are all apply the exam that records stored. like following
Examapplication
Sno Registerno
1 101
2 102
3 105
4 106
5 107
6 120
7 121
8 122
I want following out put(Available details record)
101 - 102,105-107,120-122

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

Try this

;WITH Missing (missnum, maxid)

AS

(

SELECT

1 AS missnum, (select max(RegisterNo )

FROM

dbo.studentMaster)

UNION ALL

SELECT

missnum + 1, maxid

FROM

Missing

WHERE

missnum < maxid

)

SELECT missnum

FROM

Missing

LEFT OUTER JOIN

dbo.studentMaster tt on tt.RegisterNo = Missing.missnum

WHERE

tt.RegisterNo is NULL

OPTION (MAXRECURSION 0);

Take from

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

This is the Islands and Gaps problem.

As this is a university project, I won't just give you the answer. However, take a look at this --> http://www.manning.com/nielsen/SampleChapter5.pdf%5B/url%5D.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

sql_lock (12/7/2012)
Try this
;WITH Missing (missnum, maxid)
AS
(
SELECT
1 AS missnum, (select max(RegisterNo )
FROM
dbo.studentMaster)
UNION ALL
SELECT
missnum + 1, maxid
FROM
Missing
WHERE
missnum < maxid
)
SELECT missnum
FROM
Missing
LEFT OUTER JOIN
dbo.studentMaster tt on tt.RegisterNo = Missing.missnum
WHERE
tt.RegisterNo is NULL
OPTION (MAXRECURSION 0);
Take from

Oh, be careful now. There are two things wrong with such a thing. The first is that it's a Recursive CTE that counts and is frequently slower than using even a WHILE loop. See the following for more on that subject.

http://www.sqlservercentral.com/articles/T-SQL/74118/

The second thing is that if you have offset ranges with large gaps between the ranges, you could be calculating for a very, very, long time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vs.satheesh SSCrazy Points: 2392 More actions · Answer 4

HI Still i am not getting answer.One small changes I use only one table,that table i store a values like a

registerno

101

102

103

104

105

106

107

109

110

120

121

122

123

124

125

127

129

130

I want following result like following manner

101-107,109-110,120-125,127,129-130

Please help me

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

vs.satheesh (12/7/2012)
HI Still i am not getting answer.One small changes I use only one table,that table i store a values like a
registerno
101
102
103
104
105
106
107
109
110
120
121
122
123
124
125
127
129
130
I want following result like following manner
101-107,109-110,120-125,127,129-130
Please help me

Actually, you got one of the best answers available and you didn't take the time to look or you'd have the problem solved by now. Please see the link that Cadavre gave you. It's one of the best answers possible because it shows you how to do this several different ways and the performance ramifications of each.

Don't forget that this "university project" is for a grade and, I for one, expect you to do a little work on your own to earn your grade because, someday, you're going to be on someone's doorstep askig for a job. If it's for a company that I rely on for some service, then I'd really like its empoyees to know what they're doing. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

vs.satheesh (12/7/2012)
HI Still i am not getting answer.

Have you had a read through this --> http://www.manning.com/nielsen/SampleChapter5.pdf%5B/url%5D ? The sample chapter that I linked shows the "guru" method for performing what you want to do, along with lots of explanation.

If you have and are still encountering issues, then post what you have tried so far and I'll happily help. I'm unwilling to do all of the work for you, just as I'd be unwilling to do all of the work for anyone else that posts here but I'm more than happy to help.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/