Find missing values in a sequence (sql)

Question

Table1

Empid    number
----------------
100         1
100         2
100         4
100         5
100         6
101         1

I'm self learning SQL, and a task I've come across is finding the missing values in sequence up to 12 and out putting which empid is associated.

I've attempted an approach that takes the above table and starts like

SELECT a number +1 , Min("through), MIn(by number) - 1

The entire approach use the existing numbers to find the missing "next/previous number. I'm able to output which numbers are missing. However I do not know how to group it with the associated id.

I also feel like I've complicated the task, I'm looking for guidance from anyone who can help on the best / most efficient way of going about this

create a numbers table 1-12. cross join it to a distinct list of empID's then left join that to your table1. where table1.number is null. — xQbert
– xQbert, Commented Sep 20, 2017 at 16:42
The sequence is in the number table, 1,2,3,5,6 for example will show that 4 has been skipped — Owari Jules
– Owari Jules, Commented Sep 20, 2017 at 17:17

Gordon Linoff · Accepted Answer · 2017-09-20 16:42:46Z

1

Assuming that all empids and numbers are in the table somewhere, you can do this with a cross join and filter. In MS Access, this looks like:

select e.empid, n.number
from (select distinct empid from t) as e,
     (select distinct number from t) as n
where not exists (select 1
                  from t
                  where t.empid = e.empid and t.number = n.number
                 );

This will not quite work for the data you have supplied. To handle that situation, you need a table that has the 12 numbers you are looking for.

answered Sep 20, 2017 at 16:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xQbert · Accepted Answer · 2017-09-20 16:47:05Z

0

Assumes you create a numbers table having Number column with 12 records value 1 to 12.

SELECT N.*, E.*
FROM NUMBERS N
CROSS JOIN (SELECT Distinct EmpID FROM table1) E
LEFT JOIN table1 T
  on T.EmpID = E.EmpID
 and T.Number = N.Number
WHERE T.EmpID is null

or substitute a derrived table for numbers table above

something like

(Select 1 as Number UNION ALL
Select 2 as Number UNION ALL
Select 3 as Number UNION ALL
Select 4 as Number UNION ALL
Select 5 as Number UNION ALL
Select 6 as Number UNION ALL
Select 7 as Number UNION ALL
Select 8 as Number UNION ALL
Select 9 as Number UNION ALL
Select 10 as Number UNION ALL
Select 11 as Number UNION ALL
Select 112 as Number)

I cant remember if MS Access will let you do this though...

answered Sep 20, 2017 at 16:47

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

1 Comment

Erik A Over a year ago

You can do some trickery in MS Access to create a more flexible derived table:

SELECT DISTINCT Abs(Ones.ID Mod 10)+Abs(Tens.ID Mod 10)*10 AS Sequence FROM MSysObjects AS Ones, MSysObjects AS Tens WHERE Abs(Ones.ID Mod 10)+Abs(Tens.ID Mod 10)*10  BETWEEN 1 AND 12

generates a sequence from 0 to 99, and the WHERE filters out the ones you don't want. MSysObjects always contains enough IDs to make this work, even in an empty database.

Collectives™ on Stack Overflow

Find missing values in a sequence (sql)

2 Answers 2

Comments

1 Comment

Your Answer

Collectives™ on Stack Overflow

Find missing values in a sequence (sql)

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related