Solved

Crystal report needed to show immunizations not in medical record

Posted on 2014-09-22
5
420 Views
Last Modified: 2014-09-23
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
Comment
Question by:Joe McHale
  • 2
5 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40337847
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
 

Author Comment

by:Joe McHale
ID: 40337874
I need to see who have not had any in the list
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 250 total points
ID: 40337921
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 40338240
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now