Using the database tables above, write a query that will list the patientID and the drug names of ordered drugs marked as “Complete” for all patients with a diagnosis of colon cancer.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

PART 1

Background: Oftentimes, we receive data from customers that are “messy.”  They can be missing supporting metadata that helps to properly identify a target concept, or it can be incorrectly represented.  As a terminologist, one should be able to analyze this data and make the best-educated guess for an appropriate mapping.

Scenario: A customer has provided the following tables that may or may not include messy data, and as a terminologist, you are being asked to harmonize the diagnoses, drugs, and labs to a common reference terminology?

Assumptions: 

• Aside from the primary key, none of the fields are required.
• All identifiers with the same column name and column value are references to the primary key (PK) in its respective table.

Table: PatientDiagnosis

PatientID	DiagnosisName	DiagnosisCode	InitialDiagnosisDate	DiagnosisStatus
1001	Diabetes mellitus without mention of complication, type II or unspecified type, not stated as uncontrolled	250.00	01-12-03	Active
1001	Malignant neoplasm of breast (female), unspecified	174.9	07-20-05	Inactive
1001	Malignant neoplasm of other and unspecified sites of male breast	175.9	02-03-01	Active
1002	Cancer of the Colon	C18.0	03-09-13	Active
1002	Hypertension	401	09-27-06	Active
1003	Malignant neoplasm of bronchus and lung, unspecified	162.9	04-18-13	Active
1004	Metastatic colon cancer	153.9	05-28-13	Active

Table: DrugFormulary

DrugID (PK)	DrugName	Strength	Dose Form	Route
5000	Oxaliplatin	NULL	NULL	IV
5001	Metformin hydrochloride 500 MG Oral Tablet	NULL	NULL	NULL
5002	Paclitaxel	5 mg/ml	Injectable Suspension	IV
5003	Metoprolol	NULL	Tablet	Oral
5004	Xeloda	500 mg	NULL	NULL
5005	Fluticasone propionate	0.1 mg/actuat	Inhalant Product	NULL
5006	Ipilimumab	5 mg/ml	Injectable Solution	NULL

Table: DrugOrder

DrugOrderID (PK)	PatientID	DrugOrderDate	DrugID	OrderStatus
6000	1001	03-12-13	5001	Complete
6001	1002	07-21-09	5000	Complete
6002	1002	05-08-13	5000	Discontinued
6003	1002	03-14-13	5003	Complete
6004	1001	07-20-05	5002	Complete
6005	1001	09-12-10	5000	Discontinued
6006	1003	04-18-13	5005	Complete
6007	1004	05-28-13	5004	Pending
6008	1004	05-28-13	5000	Complete
6009	1004	04-13-08	5004	Complete

Question #1:

1. Using the database tables above, write a query that will list the patientID and the drug names of ordered drugs marked as “Complete” for all patients with a diagnosis of colon cancer.
2. What is the expected result set from this query?
3. Write a SQL query that lists the number of times each drug was ordered after 2010.

question #2:

1. Given your knowledge of standard clinical terminologies, what reference terminologies can you use to harmonize diagnoses? For the given sample data, which clinical terminology would you use in creating a map set?  What was the reason for your choice over the other possibilities?
2. Write a SQL query that will extract the list of all distinct diagnoses, and any relevant metadata, to be used for diagnosis mapping.
3. What is the expected result set from the query?
4. With the expected result set from question 2c, create a map set of source to target diagnoses, where the target diagnosis is the reference terminology that you decided to use in Question 2a.Indicate which sources you used to find the corresponding codes.
5. Comment on anything you might find or encounter in the process of creating the diagnosis map set. Did anything you find affect how you created the map set? 

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme