September 6, 2011 at 2:48 am
Can't use CTEs, we are on SQL2000 🙁
We are on SQL2000 aren't we???
Seriously though, normalising the data will make your life a lot easier. You can store it in the database in this form and normalise it using temp tables, or better to store it normalised and present it in a view to the users - depends on how much control you have over the processing rather than the reporting.
Table for students Key = StudentID
Table for Courses Key = CourseID
Table for Terms Key = TermID
Xref for courses for terms Key = CourseInstanceID FK = CourseID, FK=TermID
Xref for Students on courses Key = EnrolmentID FK = CourseInstanceID, FK = StudentID
now you can interrogate students on courses, courses taken by term
you can move a course from one term to another simply by changing the termID on the Xref table
You can move a student enrolment from one term to another by changing the CourseInstanceID on the enrolment XRef
You still have not answered the question whether it is important that course AA is on the same row as DD, EE, FF or whether it is acceptable to have them associated to BB. I suspect from your further examples that it doesn't matter as long as the right students are listed as taking the right courses in the right term
Obiron
September 6, 2011 at 8:13 am
JBANKO (9/5/2011)
Two things kinda negate that. The tables are inviolate. They are what they are and can't be changed, been there since the 5th day of creation.
I doubt it. Seriously.
I don't think there was "FALL2012" at the 5th day of creation.
The people who have to look at this data want it formatted that way
No problem.
They want to see it this way? Show them.
It's named "view".
Ever heard about it? 😉
Try it and you'll see - it's really easy to build.
And it will eliminate the problem of refreshing the table every season.
The view, if written correctly, will show specified number of oncoming terms any time.
And this view will update itself, according to the current system date.
_____________
Code for TallyGenerator
September 7, 2011 at 2:37 am
@ JBANKO
What part(s) of the application are you responsible for?
Are you just trying to generate reports or are you trying to improve functionality (e.g. get the application to automatically reassign students, tutors and rooms based upon the current next term configuration)
What access to the underlying tables do you have. You say that the data is n rows per student with terms shown as columns with a class in each column and if necessary additional rows per student but what happens next year? is a new record created or do all the terms move up one column each time a term is completed. The data structure appears to be 'Bonkers!!' - are you sure that you are not already looking at a view or derived table. Normalising the data will make both application maintenance and reporting Soooooo much easier.
In a transactional system, there are very few legitimate arguments for NOT taking 3rd Normal Form for data storage (different arguments apply for Data Warehouseing and reporting systems)
If you can't or won't transform this ugly schema then you are going to continue to have problems. If it's any consolation, lots of systems do it. I worked with one database (flat file structure) where order lines were stored 3 to a record and if you have more than 3 order lines a second and then subsequent records were created. I am currently working with a health care system where the consultation record is extracted with Activity1,Activity2,Activity3,Activity4 - What happens if you have 5 activities???? As you have found, not normailsign the data causes your joins to be horrible.
September 8, 2011 at 7:40 am
A. I have stated before that I CAN'T post exact code because I would not be employed for very long if I did.
B. I did come up with a way to do this both with a cursor in SQL AND a VB process in Excel so I have solved the problem
C. Currently the application is around 2500 tables (an awful lot of which are full of NULLs or empty)
D. The front end is done in VB and breaks just about every rule of Human Interface design i.e. windows with 25+ tabs and sub windows, patterned fields which make text virtually unreadable, inconsistent placement/ordering/labeling of buttons.
E. I am not asking anyone to write code for me, just trying to find a quicker way of sorting thru mounds of data.
F. I am aware of views but have not used them in this case because the best implementation I could put together runs considerably slower than what I currently have.
G. If what I present here seems like I am asking to have my job done for me then don't respond.
I do appreciate all the ideas that have been presented and the responses I've gotten. As has been stated by another poster the only thing that matters is an advisor/lender/parent/benefactor/student can get a quick look at a tentative schedule as long as they see which term they are taking what course.
Thanks,
Joe B
September 8, 2011 at 9:26 am
Joe,
It seems you are getting a bit frustrated with the comments about reoranising the data - we have all been there. Following on from the previous posting which gave a script to populate a table with the sample data, have a look at this which takes that data and creates it into a table called Students (one record per ID) and a table called Enrolments which contains the student, the term number and the class (one row per student/term/class) You can then dump this data directly into Excel and display as a pivot table which gives maximum flexibility on reporting.
USE [tempdb]
GO
IF OBJECT_ID('tempdb..jbanko','U') IS NOT NULL DROP TABLE jbanko;
CREATE TABLE [dbo].[jbanko]( [ID] [int] NULL, [Term1] [varchar](2) NULL, [Term2] [varchar](2) NULL, [Term3] [varchar](2) NULL, [Term4] [varchar](2) NULL) ON [PRIMARY]
GO
INSERT INTO [dbo].[jbanko]([ID], [Term1], [Term2], [Term3], [Term4])
SELECT 10, 'AA', NULL, NULL, NULL UNION ALL
SELECT 10, NULL, NULL, NULL, 'DD' UNION ALL
SELECT 10, NULL, NULL, 'CC', NULL UNION ALL
SELECT 11, NULL, NULL, NULL, 'AA' UNION ALL
SELECT 11, NULL, NULL, 'DD', NULL UNION ALL
SELECT 11, NULL, NULL, NULL, 'BB' UNION ALL
SELECT 10, 'BB', NULL, NULL, NULL UNION ALL
SELECT 10, NULL, 'EE', NULL, NULL
SELECT DISTINCT ID into #Student from jbanko
SELECT DISTINCT ID,1 as 'Term',term1 as 'Course' into #Enrolment from jbanko where Term1 is not null
insert into Enrolment (ID, term, course) (SELECT distinct ID,2,term2 from jbanko where Term2 is not null)
insert into Enrolment (ID, term, course) (SELECT distinct ID,3,term3 from jbanko where Term3 is not null)
insert into Enrolment (ID, term, course) (SELECT distinct ID,4,term4 from jbanko where Term4 is not null)
select * from #Enrolment
you can now quickly list all courses for a student, all students on a course, all terms a course is running, all courses for a term etc....
In reality your data will be more complex and you may need additional data like enrolment date, student type, GPA, previous modules passed - you know the stuff.... but the principle holds true
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply