August 27, 2009 at 2:34 pm
I have a table called X
there are three columns,
code int
group varchar
number int
I want a report which list for me, the numbers which are missing in a sequence group by code and group, example
code group Number
123 A 1
123 A 2
123 A 100
123 B 1
123 B 2
123 B 500
The report will show me,
code group Missing
123 A 3 to 99
123 B 3 to 499
I think it's a piece of cake for you!!!
Thanks!!
August 27, 2009 at 2:37 pm
And what have you done to try and solve this problem?
August 27, 2009 at 2:40 pm
While you are at it go ahead and follow the top link in lynn's signature. Please post the DDL scripts and some sample data.
August 27, 2009 at 2:41 pm
Many things,
But unfortunatelly I'm not getting there,
I just don't know how to do, if you any idea...
August 27, 2009 at 2:43 pm
What do you want done if there are multiple gaps? If, for example, A had 1, 5, and 100, instead of 1, 2, and 100. That would mean a gap of 3-4, and a gap of 6-99.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2009 at 2:43 pm
Point of the question was actually to have you show us what you have tried. Post your code.
August 27, 2009 at 2:46 pm
Check out my blog here http://sqlandthelike.blogspot.com/2009/03/blog-post.html for how to build the contiguous range, then its a case of taking the inverse.
August 27, 2009 at 2:48 pm
What do you want done if there are multiple gaps? If, for example, A had 1, 5, and 100, instead of 1, 2, and 100. That would mean a gap of 3-4, and a gap of 6-99.
- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
It will show a gap 3-4 and a gap of 6-99!!
August 27, 2009 at 3:29 pm
andersonrj18 (8/27/2009)
What do you want done if there are multiple gaps? If, for example, A had 1, 5, and 100, instead of 1, 2, and 100. That would mean a gap of 3-4, and a gap of 6-99.- GSquared
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
It will show a gap 3-4 and a gap of 6-99!!
As two rows? As two entries in one column? You need to be specific on this kind of thing. If you send someout out to buy you a screw, and forget to tell them you're building a cabinet and need a 1 1/2 inch, self-tapping, wood screw, Phillip's head style, preferably in bronze coloring, don't be too surprised if they come back to you with a girl named Kandi instead of what you actually sent them out for. Get specific. It's necessary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply