RE: [sqr-users] Need Help with Evaluation

["Wagner, Jennifer A" <WAGNERJ@cder.fda.gov>]

Thanks, everyone.

Unfortunately, the statuses aren't really assigned an important value.
There is a sort of hierarchy, like this:

1 -- Pending
2 -- Ongoing
3 -- Terminated
4 -- Delayed
5 -- Submitted
6 -- Fulfilled
7 -- Released

The problem is, it's sort of a sliding heirarchy.  Certain ones can only go
up, but others can slide back.  For instance, pending can only go up.
However Delayed can slide back to Ongoing.  Terminated can go to delayed,
submitted or released.   So, it gets confusing because what I would need to
do is find the "highest" statuses and then find the most recent date...but
it can't be based on pure number in the hierarchy because of the way delayed
can slide back to ongoing.   And not necessarily is delayed always greater
than terminated.  Ah, it's really confusing.  Maybe I should base it on
date.  The reason they don't want it based purely on date is because if they
are entered out of order.  Argh.  This is to initially populate fields.
I've already created a database trigger that will work to keep the fields
populated with the most current status.

I think I'm going to play around with the dates because I think that might
be the way to go.

Thanks,


Jenny

-----Original Message-----
From: Alexander, Steve [mailto:Steve.Alexander@ci.sj.ca.us]
Sent: Friday, January 24, 2003 5:20 PM
To: 'sqr-users@sqrug.org'
Subject: RE: [sqr-users] Need Help with Evaluation


Ideally, when you design the status codes, you would make them numbers, in
order of importance, with space between the codes (10, 20, 30) so you could
insert new codes later.  Then you could work in SQL with a sub-select using
the max or min functions.

Yeah, right - that'll happen.

If the codes are in random order (U for unimportant, M for medium
importance, V for very important) then you could use the SQL encode function
(I think that's what it's called) to map the actual values into values you
can sort.  But the performance won't be as good as if they were already had
sortable values.

A way I would do it, completely in SQR, first initialize $status_list:

let $status_list = ' unimportant medium veryimportant '  ! note spaces or
other separators before, between, and after codes

Second, initialize #max_pos for each new value of &cmt_id:

let #max_pos = 0

Third, have this code for each value of &status:

let #pos = instr(, ' ' || &status || ' ', 1)  ! surround &status with space
or your separator
if #max_pos < #pos
  let #max_pos = #pos
end-if

Fourth, when I'm finished with all the values of &status:

let #end_pos = instr($status_list, ' ', #max_pos + 1)  ! search for the next
space or your separator
let $most_important_status = substr($status_list, #max_pos + 1, #end_pos -
#max_pos - 1)

-----Original Message-----
From: Wagner, Jennifer A [mailto:WAGNERJ@cder.fda.gov]
Sent: Friday, January 24, 2003 12:56 PM
To: 'sqr-users@sqrug.org'
Subject: [sqr-users] Need Help with Evaluation


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

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