Program problem

[Jennifer Wagner 301-827-3943 FAX 301-594-6463 <WAGNERJ@CDER.FDA.GOV>]

Again...  and it is probably something soooo simple!

I have a request for a list of number of applications which have had
activity on them before 1996 but no activity (except for AUT letters)
since 1996.

Now, I started out with this program (I've since tried a zillion):

begin-select
dmf_no &dmfno
from dmf_document
where to_char(stamp_date,'DD-MON-YYYY') < '01-JAN-1996'
having count(dmf_no) > 1
minus select dmf_no from dmf_document
where to_char(stamp_date,'DD-MON'YYYY') > '31-DEC-1995'
and doc_type <> 'AUT'
having count(dmf_no) < 1
end-select
end-procedure


I wasn't sure how to put the AUT letters in, but I've tried it every
which way but loose.  Also it wouldn't let me to do to_date, which is
what I've been using.  No matter how I run this program, I get dmf_nos
for things with action since 1996.  Since I am taking from the
dmf_document table, only actionable items would be put in there.  The
data entry clerks would enter the dmf_no and what type of action it is
(AUT, ANN, LET, MEM, REV, etc...).  So, it would seem that getting all
numbers that have had any action (> 1) before 1996 and then subtracting
out all those that have any no action (< 1) should work.  But it
doesn't!  Help?!

Thanks,


jenny