Query to display missing sequence numbers/range for separate groups/subgroups

Posted on 2010-08-23
Last Modified: 2012-09-20

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.
Question by:askasp
  • 8
  • 5
LVL 58

Expert Comment

ID: 33507825
Would you care to put out some result rows complete with columns desired?
LVL 58

Expert Comment

ID: 33507860
This gives you the missing numbers
;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

Open in new window


Author Comment

ID: 33507869
This was my reference when I started to build up my query.

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.
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

LVL 58

Accepted Solution

cyberkiwi earned 500 total points
ID: 33507874
As ranges instead of individual records

select A.CategoryId, A.SubCategoryId, isnull((
      select top(1) c.sequenceNumber from TableA C
      where A.CategoryId=C.CategoryId and A.SubCategoryId=C.SubCategoryId
      and c.sequenceNumber<A.sequenceNumber
      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.SubCategoryId
      and B.sequenceNumber=A.sequenceNumber -1)

Author Comment

ID: 33507956
I think this is working so far at least with my sample data, I will be able to test it with the actual data at work tomorrow.

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?

LVL 58

Expert Comment

ID: 33508038
The where clause finds sequences that are hanging (has no immediately preceeding sequence number).
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.
LVL 58

Expert Comment

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

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.SubCategoryId

Author Comment

ID: 33508061
Thank you very much. I will try it out and can test better at work tomorrow. I will let you know the first thing in the morning.

Author Comment

ID: 33508072
Well actually for that I do have a query that double checks for unassigned values and identifies them for each Category and Subcategory.

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 :)

Author Comment

ID: 33512643

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.

Author Comment

ID: 33513129
Ok so after playing around a little bit , this is what I have so far

select Category.CategoryName, SubCategory.SubCategoryName
, isnull((
select top(1) c.sequenceNumber from TableA C   --inner join
where A.CategoryId=C.CategoryId and A.SubCategoryId=C.SubCategoryId
and c.sequenceNumber1 and not exists (
select * from TableA B       --inner join A on
where A.CategoryId=B.CategoryId and A.SubCategoryId=B.SubCategoryId
and B.sequenceNumber=A.sequenceNumber -1)
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.

Author Comment

ID: 33516651

I think I am happy with the way it is right now after tweaking it a little bit more. Thank you for your help.


Author Closing Comment

ID: 33516658
Solution was clear and put me in the right direction.

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 33
Procedure syntax 5 38
TSQL Challenge... 7 35
shrink table after huge delete 2 13
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question