Re: [sqr-users] Need Help with Evaluation

["George Jansen" <GJANSEN@aflcio.org>]

I'm assuming you have a table, call it status_ranks, with fields status
and rank. If you don't, you really should. Then this becomes:



SELECT c.cmt_id, s.status
FROM pmc_commitments c, pmc_statuses s, status_ranks r
WHERE c.cmt_id = s.cmt_id
    AND s.status = r.status
    AND NOT EXISTS
       (SELECT 1 FROM pms_statuses s1, status_ranks r1
        WHERE s1.status = r1.status
             AND s1.cmt_id = s.cmt_id
             AND (r1.rank > r.rank
                      OR (r1.rank = r.rank
                              AND s1.datefield > s.datefield)));


>>> WAGNERJ@cder.fda.gov 01/24/03 03:55PM >>>
Hi,

I used to be on this group, but somehow stopped receiving mail... that
was
okay because I stopped using SQR....until now...


Anway, I have a tricky SQR to write (at least, it seems tricky).   I
have a
record, cmt_id, in Commitments (cmts) which has many statuses in the
Statuses table.   What I want to do is find the "highest" or "most
important" status and insert that current status in the cmts table.

So, I need to get all the statuses for the rec cmt_id and then compare
them.
It's not that easy, because even if they both have the status Pending,
then
we want to take the one with the most recent date.   Same goes if it
is
Delayed or Ongoing, because they are on the same level of importance,
so
we'd want the latest one.

I'm thinking of an evaluation statement, but I'm not even sure how to
start
it.

I've selected all the statuses for the particular cmt_id like this:

begin-procedure get_cmt
select cmt_id &cmt_id
  do get_stat
from pmc_commitments
end-procedure

begin-procedure get_stat
select status &status
from pmc_statuses
where cmt_id = &cmt_id


Okay, that returns the cmt_id and all it's statuses.... now how do I
go
about comparing the different statuses so that I can pull the "most
important" one (based on the criteria I have).

Thanks a ton in advance!!!


jenny

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users