Solved

Crystal report needed to show immunizations not in medical record

Posted on 2014-09-22
5
433 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 101

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 101

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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

635 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