Complex checking with SQL

In 1997 I have made a (sort of) complex system using MS Access 2.0. The
trickiest part of it was a kind of number checking: several customers
receives groups of numbered parts, that must be retorned later. As the
parts where identical we have registered only the first and last number
of the cluster a client receives.

I could represent this information in a table like this:

cluster (
    cluster_id    serial,
    serial_start    integer,
    serial_end    integer,
    date_sell    date,
    customer    integer );

As the parts returns, it's arrive is registered in another table, like
this:

arriving_parts (
    serial        integer,
    date_arriving    integer )

There was some questions the system must answer:

1. Did all the parts of a given cluster returned? If not, what are the
parts that did not returned?

2. Did the parts of a cluster returned in an ordered fashion? If not,
what are the parts that are out of order? If all the parts did not
returned yet, the parts that have returned must be treated as a cluster.

This way, if a certain customer received parts 1-100 and have returned
parts 1-25 and 28-33, 35, 37, and 39-100, the first question must be
answered as:

'Parts 25-27, 34, 36, 38 did not returned'.

If parts 2, 5, and 9 have returned after part 10 (in other words, they
are out or the order), que second answer must point these parts as out
of the order.

The first question can be answered if I can generate a pseudo-relation
or temporary table with the part numbers of a cluster, and select the
parts that are in it and not in the arriving_parts relation. If this
select returns 0 (zero) itens, then all parts have returned. Other way
to respond this question is to count the parts that have arrived and
compare this number with the number of parts of the cluster, but still
we have the problem of listing the parts that have not arrived.

The second question can be answered by seeing if the serial numbers of
the returned parts are in the same order as the date they has arrived.
Selecting the ones that are out of the order is more complex.

I remember that the program reads the relations to arrays and all the
logic was implemented via 'for' loops. There are a way to implement this
using only SQL?

TIA
--
César A. K. Grossmann
ckant@usa.net
http://members.xoom.com/ckant/
Para os sádicos e corajosos e infantis:
http://www.halcyon.com/sciclub/cgi-pvt/instr/instr.html