I’m trying to produce a list of missing numbers from the ID sequence of a table.
I’ve been searching for ideas with little luck and have attempted the following. I can’t seem to even get past the syntax error level. Could that be quickly reviewed to see if I’m even in the ballpark> Thank you in advance, Joe
– Object is to find missing numbers in column (MemberID)
– in a range from 1 to 246 (range not in yet??) From MemberMaster table
SELECT “MemberID” FROM “MemberMaster”
CASEWHEN MAX(“MemberID”) = COUNT()
THEN MAX(“MemberID”) + 1 – only goes to 1 higher than biggest in the list foe now
WHEN MIN(“MemberID”) > 1 THEN 1
WHEN MAX(“MemberID”) <> COUNT()
THEN (SELECT MIN(“MemberID”)+1
FROM “MemberMaster”
WHERE (“MemberID” + 1)
NOT IN (SELECT “MemberID” FROM “MemberMaster”))
ELSE NULL END
FROM “MemberMaster”
This should give you a usable list:
SELECT (t1."MemberID" + 1) as gap_starts_at,
(SELECT MIN(t3."MemberID") -1 FROM "MemberMaster" t3 WHERE t3."MemberID" > t1."MemberID") as gap_ends_at
FROM "MemberMaster" t1
WHERE NOT EXISTS (SELECT t2."MemberID" FROM "MemberMaster" t2 WHERE t2."MemberID" = t1."MemberID" + 1)
HAVING gap_ends_at IS NOT NULL
Based on info from this post.
Thank you. Not quite what I was going for, but this works.