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.Computer Science

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:

Instructions Files

Computer Science Experts

expert
Eleanor Ken
Computer Science

62 Answers

expert
Amit
Computer Science

50 Answers

View More Experts
Disclaimer

The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.

Get Free Quote!

262 Experts Online