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.

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