Finding missing values in known sequences?

I have a tables which store repeated measurements for a group of patients who are tested a numbered series of visits, eg. 10 visits, numbered 1 to 10.

    Patient#, Visit#, value_1,...,value_n Not all patients began the series at the same time, so at any point in time each may have completed a different number of visits. Some of the tables have a measurement for each patient at every visit, while others will have visits where no data is scheduled to be taken.

I am looking for an elegant sql or pl/sql solution to finding out which patients are missing records for scheduled visits. I need be able to ask the question at any time in the visit sequence. I have a sql solution which uses a 'not exists' type of subquery, but it fails when two visits in a row are missing. It is also relatively slow because of the subquery.

I can't help thinking that there is an entirely different approach which will compare the records for each patient against the known sequence. Any ideas would be most appreciated. Received on Wed Sep 01 1993 - 22:27:47 CEST