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? 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. 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. The LineItem class will represent an individual

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

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of