Solved

Crystal report needed to show immunizations not in medical record

Posted on 2014-09-22
5
426 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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