Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Hello,

I have been struggling with this query for a while now. Table structure is:

Table A : Aid, sequenceNumber, CategoryId, SubCategoryId

TableCategory: CategoryId, CategoryName

TableSubCategory: SubCategoryId, SubCategoryName

For each subcategory the sequence number will reset as well as for each category so for example:

For Category A, subcategory A it will be 1,2,3...n

For Category A, subcategory B it will be 1,2,3...n

For Category B, subcategory A it will be 1,2,3...n and so on . It will always start from 1 to whatever.

I would like to write a query to find missing values in a sequence which is grouped by category and by subcategory as well as gaps - 'from', 'to' values

So for example, I should be able to tell values 2,3 and 4 through 8 are missing in Category A, subcategory B and so forth... I was able to write it without groupings but I am not sure how to handle 2 tier grouping and make sure each group and subgroup have the correct sequence order.

Let me know if I am not clear and any help would be appreciated.

I have been struggling with this query for a while now. Table structure is:

Table A : Aid, sequenceNumber, CategoryId, SubCategoryId

TableCategory: CategoryId, CategoryName

TableSubCategory: SubCategoryId, SubCategoryName

For each subcategory the sequence number will reset as well as for each category so for example:

For Category A, subcategory A it will be 1,2,3...n

For Category A, subcategory B it will be 1,2,3...n

For Category B, subcategory A it will be 1,2,3...n and so on . It will always start from 1 to whatever.

I would like to write a query to find missing values in a sequence which is grouped by category and by subcategory as well as gaps - 'from', 'to' values

So for example, I should be able to tell values 2,3 and 4 through 8 are missing in Category A, subcategory B and so forth... I was able to write it without groupings but I am not sure how to handle 2 tier grouping and make sure each group and subgroup have the correct sequence order.

Let me know if I am not clear and any help would be appreciated.

```
;with A AS (
select CategoryId, SubCategoryId, max(sequenceNumber) as MaxSeq
from TableA
group by CategoryId, SubCategoryId)
select A.CategoryId, A.SubCategoryId, v.number
from A
inner join master..spt_values v on v.type='P' and v.number between 1 and A.MaxSeq
left join TableA B on A.CategoryId=B.CategoryId and A.SubCategoryId=B.SubCategoryId and B.sequenceNumber = v.number
Where B.sequenceNumber is null
```

http://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/

But I get stuck when I tried to implement 2 levels of grouping. Query is to double check the sequence order so the result set would only contain information about the missing values. An example would be:

Table A_Name CategoryName SubcategoryName MissingValueStartAt MissingValueStopAt

test A A1 20 20

test15 A A2 3 8

test20 B B3 123 123

test24 C C4 47 50

So for Category A, subcategory A1 - I am missing the value "20".

For Category A , subcategory A2 - I am missing values 3,4,5,6,7,8

For Category B, subcategory B3 - I am missing the value "123".

I hope it is a little bit more clear, if not I'd happy to try again.

Have two questions though:

- What happens if sequenceNumber hasn't set and is NULL for the entire SubCategory? seems like it ignores it?

- Can you explain the purpose of isnull and "select top(1)" in your query?

So, given a range like 1,3,4,5,8,9, it will produce 3 and 8.

The top 1.. order by sequencenumber desc goes backwards to find the first (largest) number after the gap.

For 8, it will find 5 and for 3, 1.

If it does find a number, we add 1 to it - that is the first missing number.

If it does not find any, for example a sequence like 4,5,6,7,9 (there is no number before 4), it returns null and we sub that for 1.

Hope that helps.

Exactly. Without a Max sequenceNumber to work with, what should it return?

Do you want to identify this group based on every possible conbinations of CategoryId x SubCategoryId that does not exist in TableA?

union all

select C.CategoryId, S.SubCategoryId, 1 [From], 1 [To]

From TableCategory C

Cross join TableSubCategory S

left join tableA A on A.CategoryId=C.CategoryId and S.SubCategoryId=A.SubCateg

The application I am developing is at its final stage where user will re-order each entry the way they want to see on the application interface. So it will be the user re-ordering 3k plus entries around under each category and subcategory, and I was trying to implement as many checks for them to make the process a little bit easier :)

It works the way I expected; however there are couple of things I'd like to ask you for improvement.

- Is it possible to write the same query with "inner/outer join" syntax which could be more efficient?

- And again since I will be showing the names of CategoryName and SubCategoryName instead of just their id's in TableA, would it be possible to join the tables with their foreign keys so that I can access to the fields in other tables as well instead of using only self join on TableA. (CategoryId and SubCategoryId are FK's in TableA)

So maybe work off of a joined subquery and then loop through the missing values/ranges?

Thank you for your time and effort on this.

select Category.CategoryName, SubCategory.SubCategoryNam

, isnull((

select top(1) c.sequenceNumber from TableA C

where A.CategoryId=C.CategoryId and A.SubCategoryId=C.SubCateg

and c.sequenceNumber1 and not exists (

select * from TableA B

where A.CategoryId=B.CategoryId and A.SubCategoryId=B.SubCateg

and B.sequenceNumber=A.sequenc

I managed to join the tables to pull CategoryName and SubCategoryName. But I couldn't get the other ones right (the bold commented out text). And at the bottom I can not use inner join A as an alias since it gives an error so this is as far as I could come by tweaking your query. Thought I'd let you know to make myself more clear.

And if we can't get this one I will go with what we have.

All Courses

From novice to tech pro — start learning today.

select A.CategoryId, A.SubCategoryId, isnull((

select top(1) c.sequenceNumber from TableA C

where A.CategoryId=C.CategoryId and A.SubCategoryId=C.SubCateg

and c.sequenceNumber<A.sequenc

order by c.sequenceNumber desc

)+1,1) [From], A.sequenceNumber-1 [To]

from TableA A

where A.sequenceNumber>1 and not exists (

select * from TableA B

where A.CategoryId=B.CategoryId and A.SubCategoryId=B.SubCateg

and B.sequenceNumber=A.sequenc