﻿ Procedure & Nested Query

## Learning Module Objectives:

After completing the activities in this section, you should be able to:

• Prepare a modular SQL code
• Create nested queries
• Define and repeatedly executed SQL procedures

## Learning Material

1. Procedure SQL code Slides► Video►
2. Nested queries Slides► Video►

## Individual Assignments

No individual assignment should be completed in teams.  Submit your work in Blackboard.  Do not discuss the work with other students.

Question Related to Procedures:  The patient 457631 has been hospitalized at age 58.08 with the 5 diagnoses IV17.3, I401.9, I278.00, I786.05, I272.4, calculate what is the probability of this patient dying in the next 6 months.  To do so, write a procedure code that calculates the likelihood ratios associated with any diagnosis.  Repeatedly execute the procedure for the five diagnosis of the patient and estimate the likelihood ratios associated with this patient's death.  Predicted the odds of mortality by multiplying the five likelihood ratios.  Data►

Question Related to Nested Queries:  Write a nested query that first finds the last visit by a patient and then reports their diagnoses during the last visit. Write the same code without using nested query.  Report the difference in computational time in seconds  Data►

Ongoing Question: This question is an individual project conducted from now till end of class.  Each week a portion of the work is done.  This week construct a model to predict if the patient has diabetes.

2. Clean the data, as you had done before for predicting mortality of patients
3. Create a predictive model for diabetes.  Diabetes is defined using the Agency for Health Care Research and Quality Clinical Classification system.  Use other diagnoses to predict the risk for diabetes. CCS Codes►
4. Calculate for each diagnosis the likelihood ratio associated with its impact on predicting or detecting diabetes within 6 months.
5. Calculate likelihood ratios associated with patients age (for each year between 40 to 99) and diagnosis of diabetes within 6-months of the age. Plot the data with X-axis showing the age of the patient and Y-axis showing the likelihood ratio associated with age.
6. Calculate likelihood ratios associated with gender and lifetime diagnosis of diabetes.

None

## More

1. Extent of undetected diabetes PubMed►
2. Use of diagnoses to phenotype type 2 diabetes PubMed►

Copyright © Farrokh Alemi, Ph.D. First created on January 9th 2005. Most recent revision 08/29/2018. This page is part of the course on Healthcare Databases.