Specific criteria for record group 1

[dande]

We have a table the contains staff assignments to a client. There can be multiple staff assignments to a client by programs(which are distinct programs).
clientid_c staffmasterid_c program_c
------------------ ------------------ ---------
00A009 0406 900
00A027 0406 900
00A027 0320 500
00A055 0326 500
00A055 0406 900
00A055 0352 100
There is an order of program assignments as to which staff is responsible for directing the clients case. I'm trying to create a view to run a Crystal report from as to which staff controls the case. The program assignment order is 500 - 100 - 900. I'm having problems creating a view that would have 1 record for each client with the staff that should control the case, like below.
clientid_c staffmasterid_c program_c
------------------ ------------------ ---------
00A009 0406 900
00A027 0320 500
00A055 0326 500

Any suggestions? Thanks, Paul

 

[GShen]

Paul,
I sort of understand what you want to do. However, your output does not reflect what you are saying. For example,
where is 00A055 0406 900 in your output? Is this an SQL stored procedure you are trying to write or an ACCESS VIEW?

New more info.

Remember when... everything worked and there was a reason for it?

 

[dande]

GShen, Thanks for a quick response. I was using Query Analyzer to see if I could get the desired results before creating the view. In the results there should be only 1 staff responsible in controling the case. If there is a 500 program assignment they conrol the case regardless of other assignments. If there is not a 500 program assignment the next in order would be is there a 100 assignment and so on. For client 00A55 there is a 500 assignment so staff 0326 is the controlling staff. Clear as mud?? I hope this helps? Thanks again..Paul

 

[GShen]

Paul,
Ok, QA that is fine. What tells you the program order assignment sequence. Eg. 500, 100, 900. There has to be an identified telling you this or you are going to have a problem. You can't sort 500,100,900 but if you had something telling you 500 was 1st, 100 was 2nd, and 900 or whatever is third, I may have a solution.

Remember when... everything worked and there was a reason for it?

 

[dande]

I'm in USA. State of Ohio determines the order, it happens that that their order translate to our codes as 500-100-900. Could we use a case statement against program_c to get a workable order.

 

[GShen]

Paul,
I tried this a few different ways and it is a little complicated with views. A straight query is not the answer to the best of knowledge either. It looks like you will need to write a CURSOR for this to process. Have you done that before? I can give you a skeleton if you haven't done it before

Remember when... everything worked and there was a reason for it?

 

[dande]

GShen, I have seen, but never really undersood or worked with a CURSOR before. I'm ready and willing to be taught. Thanks for the offer. - Paul

 

[GShen]

Dande,
Sorry, I am in the middle of a big project that needs to get completed. It is basically record by record processing. I use it alot for updates. Never wrote 1 for a Select but I know it can be done for that as well. Look up in BOOKS ONLINE to get a little back round. Once you have 1 it is pretty simple. I will try and get you a working skeleton for a SELECT cursor when I get a chance.
Sorry for now.

Remember when... everything worked and there was a reason for it?

 

[dande]

Thanks for reply. I understand life. It is a department of 1 here, with many hats.. That's the main reason that I use forums like this. I'll check out the Books Online - Thanks again.