Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal report needed to show immunizations not in medical record

Posted on 2014-09-22
5
Medium Priority
?
441 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 1000 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 1000 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 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