Complex (for me) SQL query

Question

Complex (for me) SQL query

anew

Grasshopper

Points: 10
More actions
January 18, 2011 at 10:03 am

#245551

Hello everyone, I'm new here, but this looks like a great community and resourse. I am an IT Manager for a small public school system. I have done basic SQL administration in the past, but usually on databases that I designed for specific purposes. I am trying to great a query or stored procedure that till pull the percentage of students enrolled in a certain program from out student information system (SIS). I need to be able to track the percentage for each day of the school year. Since the SIS is designed by a company, I cannot change the structure of the data. The table layout with the information I need is below:
Table Student
==================
Student ID
Student Name
Student School
....
Table StudentEnrollment
==================
Student ID - Linked to ID in the student table
Enrollment Date - Date student enrolled in school
Withdraw Date - Date student withdrew (null if still in school)
....
Table StudentProgram
==================
Student ID - Linked to ID in the student table
Program ID - Program the student is enrolled in
Program Enroll - Date student enrolled in program
Program Withdraw - Date student withdrew from program(null if still in program)
....
I would love to be able to generate a table or result set that shows the Date, School, count of studentProgram on that date, and count of enrollment on that date.
I have tried a couple ideas, but I have not been successfull thus far. My latest thought was to have two functions (one to generate the count of studentprogram and one for count of enrollment given the date and schools as a variables) and call them from a stored procedure, but I don't know how to write the SP to loop throught the dates and create a table, etc.
Thanks in advance for your help.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

anew,

You've done a good job in laying out what you want; however not so great in giving us the data to actually help us help you. Can you provide the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

anew Grasshopper Points: 10 More actions · Answer 2

Thanks, I'll have to get that together. It will probably be tomorrow before I have it ready.

kalpu234 SSC Rookie Points: 38 More actions · Answer 3

I am trying to get into your problem but what i don;t understand is you are expecting to provide schoolname as input variable for the sp . Moreover I didn;t see any table mentioning school as its column and relationship existed with other tables as wells.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

kalpu234 (1/18/2011)
I am trying to get into your problem but what i don;t understand is you are expecting to provide schoolname as input variable for the sp . Moreover I didn;t see any table mentioning school as its column and relationship existed with other tables as wells.

You backtrack School out of Student and turn it into a temporary entity within your process.

There's a few things going on here. Of most import:

1) There's no calendar table either, that needs to be derived

2) The school primary list will be derived from the Student table. This is acceptable as Student drives the final count anyway.

Since the OP is getting us the original DDL and sample data, we'll see what these structures look like and make better decisions from there. 😉

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA