Q and A – Tackling the Cumulative Debt Cursor Problem
This covers the approach to take in covering COP4709 Assignment 6B – Cursors problem.
First Steps
First we must get a good feel for the actual data. After you take a very close look – you will see that this data violates some of the tenets set forth in the data dictionary (such as all the UNITID are not unqiue) – but for now we will ignore that.
Our first query will look at the total number of students used in the Debt data sample. The number of students at each school is DEBT_N field in the tables. To get a good feel for this we declare the variable @TOTAL_N and sum all the DEBT_N fields. The Next query calculates the percentage of the total that each school in the sample contributes. This is the second query (note the CAST statement for formatting) – and the (partial) results.
| DECLARE @TOTAL_N INT
SELECT @TOTAL_N = SUM(DEBT_N) from Debt
SELECT CAST(INSTNM AS VARCHAR(50)), CAST(DEBT_N AS VARCHAR(10)), 100*DEBT_N/@TOTAL_N FROM Debt
-------------------------------------------------- ---------- ---------------------- Alabama A & M University 2497 0.00440936300067659 University of Alabama at Birmingham 6034 0.0106552248082029 Amridge University 477 0.000842317241218556 University of Alabama in Huntsville 2651 0.00468130609322933 Alabama State University 4208 0.00743075671079179 The University of Alabama 9234 0.0163059903677403 Central Alabama Community College 1628 0.00287482697841469 Athens State University 1952 0.00344696699131786 Auburn University at Montgomery 2595 0.00458241769593743 Auburn University 5795 0.0102331832554749 Birmingham Southern College 476 0.0008405513769812 Chattahoochee Valley Community College 532 0.000939439774273106
|
Objectives
The objective of this query is to see how any individual school or schools compares to the average debt of all schools. This debt is not calculated as an overall debt – but is actually measured at different stages of the students completion of the degree (10%, 25%, 75%, and 90%). This information might provide some real insight as to how the student debt accumulates over the course of their studies – so is worth looking at in more detail. Let’s first look at the averages.
SELECT CONVERT(DECIMAL(6,0),AVG(CUML_DEBT_P10)) AS '10% Debt',
CONVERT(DECIMAL(6,0),AVG(CUML_DEBT_P25)) AS '25% Debt',
CONVERT(DECIMAL(6,0),AVG(CUML_DEBT_P75)) AS '75% Debt',
CONVERT(DECIMAL(6,0),AVG(CUML_DEBT_P90)) AS '90% Debt'
FROM Debt
10% Debt 25% Debt 75% Debt 90% Debt
----------------------- ------------------------ ------------------------ ------------
3530 6243 18543 25147
|
These average, however are assuming that every school has the same number of students, which is not the case. A more correct average per student debt would be obtained by weighting them based on the number of indebted students at each school which would be the formula
SUM(CUML_DEBT*CUML_DEBT_N)/SUM(CUML_DEBT_N)
SELECT CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P10*CUML_DEBT_N)/SUM(CUML_DEBT_N)) AS '10% Debt Weighted',
CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P25*CUML_DEBT_N)/SUM(CUML_DEBT_N)) AS '25% Debt Weighted',
CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P75*CUML_DEBT_N)/SUM(CUML_DEBT_N)) AS '75% Debt Weighted',
CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P90*CUML_DEBT_N)/SUM(CUML_DEBT_N)) AS '90% Debt Weighted'
FROM Debt
10% Debt Weighted 25% Debt Weighted 75% Debt Weighted 90% Debt Weighted
------------------- ------------------ -------------------- -------------------
2816 5990 27976 39153
|
Constants
At this point it is worth noting that all of these numbers are constants. So in forming a report they will not change based on the individual schools – so these can be assigned t constants. Knowing this we can reformat our query to do some interesting comparisons.
DECLARE @P10W FLOAT, @P25W FLOAT, @P75W FLOAT, @P90W FLOAT
SELECT @P10W = CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P10*CUML_DEBT_N)/SUM(CUML_DEBT_N)),
@P25W = CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P25*CUML_DEBT_N)/SUM(CUML_DEBT_N)),
@P75W = CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P75*CUML_DEBT_N)/SUM(CUML_DEBT_N)),
@P90W = CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P90*CUML_DEBT_N)/SUM(CUML_DEBT_N))
FROM Debt
SELECT CAST(INSTNM AS VARCHAR(50)),
CUML_DEBT_P10 AS '10% Institutional Debt',
@P10W AS '10% Weighted Average'
FROM Debt
WHERE INSTNM LIKE'%Alabama%'
10% Institutional Debt 10% Weighted Average -------------------------------------------------- ---------------------- ---------------------- Alabama A & M University 4750 2816 University of Alabama at Birmingham 4325 2816 University of Alabama in Huntsville 3927 2816 Alabama State University 4750 2816 The University of Alabama 5500 2816 Central Alabama Community College 2367 2816 Concordia College Alabama 2750 2816 University of West Alabama 2750 2816 University of North Alabama 3500 2816 Northeast Alabama Community College 1300 2816 Alabama Southern Community College NULL 2816 University of South Alabama 3500 2816 Alabama State College of Barber Styling NULL 2816 Strayer University-Alabama 2276 2816
|
Rotating the table (note this will only work for one institution at a time and will necessitate the use of a cursor for multiple schools generating more than one table you get the following.
DECLARE @P10W FLOAT, @P25W FLOAT, @P75W FLOAT, @P90W FLOAT SELECT @P10W = CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P10*CUML_DEBT_N)/SUM(CUML_DEBT_N)), @P25W = CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P25*CUML_DEBT_N)/SUM(CUML_DEBT_N)), @P75W = CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P75*CUML_DEBT_N)/SUM(CUML_DEBT_N)), @P90W = CONVERT(DECIMAL(6,0),SUM(CUML_DEBT_P90*CUML_DEBT_N)/SUM(CUML_DEBT_N)) FROM Debt SELECT '10% Debt Level', CUML_DEBT_P10 AS 'Institution', @P10W AS ' Unweighted Average' FROM Debt WHERE INSTNM LIKE'%Alabama A & M%' Institution Unweighted Average --------------- ---------------------- ---------------------- 10% Debt Level 4750 2816 |
Remember – anything you can query, you can place in a variable for printing.
Conclusion
From here you should be able to create a formatted report (with or without using a cursor) that meets the requirements of the initial assignment.