exclude patient records who get a particular service 1

[scottaherbst]

I'm working in CR10 on an SQL server DB.

Where I work, clients get a variety of services. If I returned all the serice records for a group of clients, it might look something like this:

Client_id Service_number Service_code
100 1 IP
100 2 SC
100 3 MM
100 4 OU
200 1 SC
200 2 MM
200 3 OC
300 1 GH
300 2 SC
300 3 MM

Now, what I want are all the records for clients who don't get particular service types - in this case "IP" and "GH". How do I write a command line that will look at each client to see if they get those services and then return the client records for that don't. After the query, the above data should look like this:

Client_id Service_number Service_code
200 1 SC
200 2 MM
200 3 OC

cleint 100 should be excluded b/c he recieves service "IP" and client 300 should be excluded b/c he gets "GH". Thanks.

 

[lbass]

Are you ONLY interested in command approaches versus using the Crystal interface?

-LB

 

[scottaherbst]

Actually, no. I'm pretty new to the whole environment so any new information would be appreciated. Thanks

 

[lbass]

An easy way to handle this is to create a formula {@hascode} in the field explorer of CR:

if {table.service_code} in ["IP","GH"] then 1 else 0

Do not use record selection criteria on the codes field. Instead, insert a gorup on {table.clientID} and then go to report->selection formula->GROUP and enter:

sum({@hascode},{table.clientID}) = 0

This will return all records for clients who don't have IP or GH in any of their records. You could replace the hard codes in your original formula to use a parameter with multiple values:

if {table.service_code} = {?Code} then 1 else 0

Note that if you then need to summarize across clients, you will need to use running totals, since inserted summaries would still evaluate the non-group selected records. Running totals, on the other hand, only pick up on the group-selected records.

-LB

 

[scottaherbst]

Brilliant. Thanks.