Re: Missing numbers

On Tue, 2005-05-31 at 18:28 +0200, Harald Fuchs wrote:
> In article <429C7B9B(dot)1040705(at)lamundial(dot)hn>,
> josue <josue(at)lamundial(dot)hn> writes:
>
> > Hello list,
> > I need to track down the missing check numbers in a serie, table
> > contains a column for check numbers and series like this:
>
>
> > dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
> > dbalm-# and doc_tipo='CHE' order by doc_numero;
> > doc_numero | doc_ckseriesfk
> > ------------+----------------
> > 19200 | 856938
> > 19201 | 856938
> > 19215 | 856938
> > 19216 | 856938
> > 19219 | 856938
>
> > Missing numbers are:
> > from 19202 to 19214 and 19217,19218
>
>
> > Does anyone knows a way to get that done in SQL or plpgsql, thanks in
> > advance
>
> You could use something like that:
>
> SELECT g.num
> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
> (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> LEFT JOIN bdocs ON bdocs.doc_numero = g.num
> WHERE bdocs.doc_numero IS NULL

SELECT g.num
FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
(SELECT max(doc_numero) FROM bdocs)) AS g(num)
WHERE g.num NOT IN (select doc_numero
from bdocs
where doc_numero is not null)

is more likely to return a correct answer, since
bdocs.doc_numero will never equal g,num when it is also NULL

Best Regards, Simon Riggs