Select non existing Numbers from Table each ID

Question

I‘m new in learning TSQL and I‘m struggling getting the numbers that doesn‘t exist in my table each ID.

Example:

CustomerID Group
1          1
3          1
6          1       
4          2
7          2

I wanna get the ID which does not exist and select them like this

CustomerID Group
2          1       
4          1
5          1
5          2
6          2
....

..

The solution by usin a cte doesn‘t work well or inserting first the data and do a not exist where clause.

Any Ideas?

It is possible but is not how SQL works. SQL is designed to get **existing** data. — juergen d, Sep 25 '19 at 12:54
not existing means getting the next higher number, I need the available CustomerIDS each group — Leo96, Sep 25 '19 at 12:57
Isn't `CustomerId` 7 missing from `Group` 1? Or has it been banished to [Group W](https://www.urbandictionary.com/define.php?term=Group%20W%20Bench)? — HABO, Sep 25 '19 at 13:35
What do u mean? The Id 7 exist already. I wanna select all ‚available‘ customerid‘s - which are not included in the first screen. — Leo96, Sep 25 '19 at 15:38
Exactly. There is a customer 7 and they aren't in group 1. Why is 7 not _available_ for group 1? Did you neglect to mention that a customer can only be in one group? If a customer 8 existed in group 1 would customer 7 have to be "eliminated"? — HABO, Sep 25 '19 at 17:29
Ohh I got it! No, it should not be eliminated. I wanna get those not existed or ‚available‘ ID‘s for each group. When CustomerID 7 is NOT in group 1 But it is in Group 2, then i wanna get the CustomerID 7 only in the Group 1, only that group, which doesn‘t contain that ID. — Leo96, Sep 25 '19 at 18:05

score 0 · Accepted Answer · answered Sep 25 '19 at 13:01

0

If you can live with ranges rather than a list with each one, then an efficient method uses lead():

select group_id, (customer_id + 1) as first_missing_customer_id,
       (next_ci - 1) as last_missing_customer_id
from (select t.*,
             lead(customer_id) over (partition by group_id order by customer_id) as next_ci
      from t
     ) t
where next_ci <> customer_id + 1

answered Sep 25 '19 at 13:01

Gordon Linoff

1,242,037
58
646
786

thanks!! can I ask you, why u working with lead? and selecting the first missing and the last missing id? – Leo96 Sep 25 '19 at 21:13
@Leo96 . . . Because it does what you want. `lead()` gets the next id. The question is whether that is one more than the id in the current row. – Gordon Linoff Sep 26 '19 at 02:48
One ID can be in several groups. U mean that? – Leo96 Sep 26 '19 at 06:24
@Leo96 . . . That has nothing to do with the answer. The `partition by` limits the comparisons within a single group. – Gordon Linoff Sep 26 '19 at 10:39

score 0 · Answer 2 · answered Sep 25 '19 at 19:50

0

Cross join 2 recursive CTEs to get all the possible combinations of [CustomerID] and [Group] and then LEFT join to the table:

declare @c int = (select max([CustomerID]) from tablename);
declare @g int = (select max([Group]) from tablename);
with 
  customers as (
    select 1 as cust
    union all
    select cust + 1 
    from customers where cust < @c
  ),
  groups as (
    select 1 as gr
    union all
    select gr + 1 
    from groups where gr < @g
  ),
  cte as (
    select *
    from customers cross join groups
  )
select c.cust as [CustomerID], c.gr as [Group] 
from cte c left join tablename t
on t.[CustomerID] = c.cust and t.[Group] = c.gr
where t.[CustomerID] is null
and c.cust > (select min([CustomerID]) from tablename where [Group] = c.gr)
and c.cust < (select max([CustomerID]) from tablename where [Group] = c.gr)

See the demo.
Results:

> CustomerID | Group
> ---------: | ----:
>          2 |     1
>          4 |     1
>          5 |     1
>          5 |     2
>          6 |     2

answered Sep 25 '19 at 19:50

forpas

160,666
10
38
76

Edit: It's working fine, thanks. The issue is the perfomance. Can I declare above the IDs and a group? For instance selecting the not existing id's between 800 and 850 for group xy. Gonna work a little bit on that exmaple and see further. – Leo96 Sep 25 '19 at 20:42
Check this: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4e278706e87639d0de4c781b622cc875 – forpas Sep 25 '19 at 20:57
It won't work using separate table inserting the data while having millions of data in my db... – Leo96 Sep 25 '19 at 21:05
What do you mean separate table? If you have such a large table make sure to have indexes for both columns. – forpas Sep 25 '19 at 21:17

Select non existing Numbers from Table each ID

2 Answers2