I'm working with some data on college students, which has a unique key of SSN
, Campus
, Credential
(type of degree sought, like "Bachelor's"), ProgCat
(broad subject area, like "History"), Program
(specific subject area, like "European History"), and SchoolYear
. I want to calculate a lot of statistics (for the sake of this example, we'll just stick with basic counts of number of students) and I want those statistics to be calculated for various combinations of aggregation of Campus, Program, and Program Category. In other words, I want to calculate statistics that get as specific as "number of students getting a bachelor's degree in European History at Big Oak University," as general as "number of students getting a bachelor's degree in anything at any university" and every level in between.
Now, usually, I'd just use CUBE
in my GROUP BY
statement, which would give me exactly what I want. However, there's an added complication... I need unduplicated counts of students, and it's possible that one student is enrolled in two different subjects, or at two different universities. So, if Jim Bob is enrolled both at Big Oak University and Sunrise College, I'd want him to show up once in the statistics for Big Oak, once in the statistics for Sunrise, and once and only once in the statistics for all colleges grouped together. As far as I know, there's no way to accomplish this with CUBE
.
I've therefore been pursuing some solutions to deal with this. What I've come up with is a dynamic SQL solution. Basically, I create a table to hold my output, then I repeatedly cycle through calling the same SQL code with different parameters and adding the output to the output table. The parameters control what level of aggregation I'm using. I use a simple subquery with row_number()
to pull only one record per student at any level of aggregation, making sure I get unduplicated counts. It looks something like this...
IF OBJECT_ID('tempdb..##CountOut') IS NOT NULL
DROP TABLE ##CountOut;
CREATE TABLE ##CountOut(Agency char(3), Credential varchar(2), Campus varchar(10), ProgCat varchar(2),
Program varchar(6), SchoolYear smallint, Students int, Graduates int);
GO;
CREATE PROCEDURE ##CalcGroup
@UseCampus bit,
@UseProgCat bit,
@UseProgram bit
AS
declare @AllList varchar(max);
declare @ShortList varchar(max);
declare @SQL varchar(max);
select @AllList='';
select @ShortList='';
if @UseCampus=1 begin
select @AllList=@AllList+'Campus, ';
select @ShortList=@ShortList+'Campus, ';
end
else
select @AllList=@AllList+'''ALL'' as Campus, ';
if @UseProgCat=1 begin
select @AllList=@AllList+'ProgCat, ';
select @ShortList=@ShortList+'ProgCat, ';
end
else
select @AllList=@AllList+'''AL'' as ProgCat, ';
if @UseProgram=1 begin
select @AllList=@AllList+'Program, ';
select @ShortList=@ShortList+'Program, ';
end
else
select @AllList=@AllList+'''ALL'' as Program, ';
select @SQL='
insert into ##CountOut(Agency, Credential, Campus, ProgCat, Program, SchoolYear, Students, Graduates)
select Agency, Credential, '+@AllList+'SchoolYear,
count(*) as Students,
count(ExitQuarter) as Graduates
from
(
select Agency, Credential, '+@ShortList+'SchoolYear, ExitQuarter,
row_number() over (partition by Agency, Credential, '+@ShortList+'SchoolYear, SSN order by ExitQuarter desc) as RN
from CFS_ESC.dbo.webapp_Data
) as Inside
where RN=1
group by Agency, Credential, '+@ShortList+'SchoolYear;
';
exec(@SQL);
GO
execute ##CalcGroup @UseCampus=1, @UseProgCat=1, @UseProgram=1;
execute ##CalcGroup @UseCampus=1, @UseProgCat=1, @UseProgram=0;
/* etc. */
Is there anything you see to improve the clarity or the performance of this code?
I'm also wondering if this is the best way to accomplish this task? The only other solution that I have thought of is to create multiple records for every student enrollment. In other words, each student would have a record with their campus, program category, and program specified, a second record with their campus and program category specified and their program set to "All", a third record with... etc. That would make the initial data I'm querying over about 6x larger, but I'd only have to run one query to get my results, and I wouldn't have to worry about dynamic SQL. Do you think that might be a better option?
Any other solutions out there?
I'd appreciate any and all feedback. I have absolutely no formal training in SQL, though I've taught myself plenty to accomplish most tasks thrown at me. I'm now just hoping to learn some best practices.