Query to display missing sequence numbers/range for separate groups/subgroups
Posted on 2010-08-23
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.