Re: Program problem

[George Jansen <GJANSEN@AFLCIO.ORG>]

How about

begin-select distinct
dmf_no
from dmf_document d
where stamp_date <= to_date('31-dec-1995', 'dd-mon-yyyy')
and not exists
(select 1
  from dmf_document d1
  where d1.dmf_no = d.dmf_no
      and d1.stamp_date >= to_date('01-jan-1996, 'dd-mon-yyyy')
      and d1.doc_type <> 'AUT'
)


>>> WAGNERJ@CDER.FDA.GOV 04/07/00 11:11AM >>>
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