could this have been done better using pl/sql

[jybrd81]

We have a table called trx (stores data related to treatments done to patients by providers). Then we have a table called producer which stores data about the provders mainly there provider num (field called producer) and there end date (field enddate). The trx table has a field called TxStatus. If the provider is a student here the TxStatus shows the year they are in. A 4th year student has a TxStatus of "YEAR 4", and so on. If the provider is no longer here they have a status of "GRAD". For reporting purposes we want to know what year the provider was when they did a certian treatment when they were a student. So for all the rows in the trx table where the TxStatus field is GRAD I need to change it to what year the provider was when they did the treatment. I figured this could be done using pl/sql in a procedure I just could not figure out how to do it. So what I did was use a blank field in the trx table and wrote some sql commands and got the task done however I was anoyed I couldn't write a procedure to do it. here is what I did.
first I found out what accademic year the treatment was done in by using the Treatmentdate field in the trx table.
code:
-- for the 2002 accademic year
update trx set "CorrespondCode"='2002' where "TreatmentDate" BETWEEN '01-AUG-02' and '31-JUL-03'
-- for 2003
update trx set "CorrespondCode"='2003' where "TreatmentDate" BETWEEN '01-AUG-03' and '31-JUL-04'
and so on
now I have in the trx table the accademic year the treatment was done. Next I needed to take the providers enddate and use that to figure out what year they were in when they did the treatment. for example if the provider did somthing in the 2003 accademic year and they were graduating in 2004 then they were in there forth and last year here. the formula is
if producer.enddate - trx.corresondcode =1 then provider was a "Year 4"
elsif
producer.enddate - trx.corresondcode =2 then provider was a "Year 3"
elsif
producer.enddate - trx.corresondcode =3 then provider was a "Year 2"

so for this I used the commands
--year 4
update trx set "TxStatus"='YEAR 4' where "Id" IN (select a."Id" from trx A, Producer b where a."Producer"=b."Producer" and substr(b."EndDate",9) - substr(a."CorrespondCode",4) = 1)
--year 3
update trx set "TxStatus"='YEAR 3' where "Id" IN (select a."Id" from trx A, Producer b where a."Producer"=b."Producer" and substr(b."EndDate",9) - substr(a."CorrespondCode",4) = 2)
--year 2
update trx set "TxStatus"='YEAR 2' where "Id" IN (select a."Id" from trx A, Producer b where a."Producer"=b."Producer" and substr(b."EndDate",9) - substr(a."CorrespondCode",4) = 3)

So this is what I did and it worked my boss got what she wanted and it did not take long to do however it was no the way I wanted to do it. I spent a day and a half trying to do this in a procedure with pl/sql and could not get anywhere. Could this have been done with a function or procedure or anything, and if so how?

[PAVB]

No reason not to wrap it up as a storedproc, specially if this is something that has to be done in a recurrent basis.

In that case I'll allow for the storedproc to get some external parameter like the academic year or so - I think the storedproc can infer all other values from that one.

[stecal]

Searched CASE statement.