I have a set of tables vorders (order) header and vitems (line items) with referential integrity set to cascade for deletes. I need to delete old records older than target date (last day of june) and wonder what the most efficient method is? The rules are as follows.

1. header file ledger date is less than june30
a. all items for order have been recieved (status flag not blank
or b)
i. if a line item an alternative invoice number it must also
have an alternate ledger date
ii, if an item was back ordered, determined by status flag, it
must have a back order invoice number and a back order
ledger date.

My two ideas are to
1, Use a SCAN loop but then it appears I have to nest them to check
line items table and I an not sure how this effects the
efficiency.

2. Use a SQL Select statement to create a list of vaild keys, but
then I am not sure how to write the selection critera to check
multiple records in line items tables for each order record and
how to get only one record per valid key.

Any suggestions would be appreciated.