Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

Crystal report needed to show immunizations not in medical record

I am using a SQL 2008 database.  I need to write a Crystal report to show what patients do not have certain immunizations.  All the immunizations are recorded in one table called order. The field I am querying lists the code associated with the immunization for example (90658, 90672,90656,90669, 90772)  I have tried a selection statement such as "not acctcode like ["90670",90732"]  This returns a list of patients and excludes those records form my list.  However it does not check to see if those items are in the table.  I do no think my logic is correct.  Can someone provide some other ways to exclude those patients with those acctcodes.
0
Joe McHale
Asked:
Joe McHale
  • 2
2 Solutions
 
mlmccCommented:
Are you wanting to input a list of immunizations and show patients who haven't had any of them or are missing one or more of the immunizations?

For example as above ["90670",90732"]

You need to see patients who haven't had either 90670 or 90732.
What about a patient who has received 90670 but not 90732?

mlmcc
0
 
Joe McHaleAuthor Commented:
I need to see who have not had any in the list
0
 
John_VidmarCommented:
Using this test data:
create table YourTable
(  acctcode char(10)
,  patientid int
);

insert yourtable select '9067A',1;
insert yourtable select '90670',1;
insert yourtable select '90732',1;
insert yourtable select '90732',2;
insert yourtable select '90670',3;
insert yourtable select '9073A',4;

Open in new window

The following would list patients who do not have both immunizations:
SELECT	a.*
FROM	YourTable	a
LEFT
JOIN	(	SELECT	patientid
		FROM	YourTable
		WHERE	acctcode IN ('90670','90732')
		GROUP
		BY	patientid
		HAVING	COUNT(DISTINCT acctcode) = 2
	)	b	ON	a.patientid = b.patientid
WHERE	b.patientid IS NULL

Open in new window

0
 
mlmccCommented:
If you need to see those who have missed 1 of the immunizations, change the HAVING clause to >= 1

DO you need to pass in a set of values for the immunizations or will you hardcode them in the query?

mlmcc
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now