Date problem (can't describe it in one line! :) )

[bujin]

I'm writing a report for a college to compare the numbers of students enrolled on a course this year with the numbers last year. Courses can be 1, 2 or 3 years in length. So far, I am calculating the enrolments on a particular year of a course as follows:

1. A student is in year 1 of a course this year if:
* Course Start date is between 1/8/AY and 31/7/(AY+1)
* Student Enrolment on the course ends after 1/MM/AY

2. A student is in year 2 of a course this year if:
* Course Start date is between 1/8/(AY-1) and 31/7/AY
* Student Enrolment > 1/MM/AY

3. on year 1 last year if:
* Course Start between 1/8/(AY-1) and 31/7/AY
* Student Enrolment > 1/MM/(AY-1)

4. on year 2 last year if:
* Course Start between 1/8/(AY-2) and 31/7/(AY-1)
* Student Enrolment > 1/MM/(AY-1)

and similarly for year 3 of the course, if applicable.
In the above, AY is the current Academic Year (which is calculated in the database), and MM is a month number entered as a parameter for a census date, so that the numbers of enrolments can be checked for any time of the year.

The problem with the above method, is that if a student enrols straight onto year 2 of a course this year (for example), he/she is still counted as on year 1 last year, because the year is worked out from the module start date. I need a way of avoiding counting the student on year 1.

Each module (course) has a specific start date (usually 1/8) and a specific end date (usually 31/7). The student has enrolment dates on the module. The enrolment start date is ALWAYS later than the module start date, and the enrolment end date can be any time during the lifetime of the module).

To give an example of the problem I'm having might make it easier to understand. Let's say I have a module which starts on 1/8/99 and ends on 31/7/01. If a student enrols onto the module on, say, 14/9/99 and is still on the course after 1/8/00, then he is on year 1 last year and year 2 this year (see points 2 and 3 above). If, however, a student enrols on the same course on 14/9/00, i.e. straight into year 2, because the student enrolment end date is still after 1/8/99, he fits criteria 2 and 3 again, and so is counted on both year 1 last year and year 2 this year.

Any tips or suggestions would be greatly appreciated.

Gareth

 

[kenhamady]

I had trouble breaking down your question.

Part of it was using both "course" to "module" to mean the same thing.

Part of it was I didn't realize that you were using European Date format until the end with 14/9. It mad much more sense when I flipped it over.

Anyway, let me see if I sorted it out. In a nutshell you are trying to distinguish between someone who enrolled in the second year vs the first year of a 2-year course?

If that is the question, you have to calculate the anniversary date of the course start (ie one year later) and compare the enrollment date to that anniversary date. To be in year one enrollment has to be before the first anniversary of the course start date. You could do the same thing with the second anniversary if needed.

Ken Hamady-

http://www.kenhamady.com

http://www.kenhamady.com/

 

[bujin]

Sorry, I should have been a bit clearer in my description - I keep forgetting that other countries use different date formats! Anyway, I've sorted it out now. It SEEMS to be working fine. I did something like what you described.

I now calculate that a student is in year one this year if:
* Module Start is between 1/8/AY and 31/7/(AY+1)
* Enrolment End is after 1/MM/AY if MM is between 8 and 12 or 1/MM/(AY+1) if MM is between 1 and 7
* Enrolment Start is between the Module Start date (MSD) and 31/7/(Year(MSD)+1)

and similar for other years.

Sorry about the confusion with 'course' and 'module', too! I always refer to them as courses, but the database refers to them as modules.

Oh, and I mentioned above that the enrolment start date is ALWAYS after the module start date. Well I found a course that defied that - people were enrolled on a course on 14/08/99 when the course didn't start until 01/09/99!!! This is a problem with the way the data was entered into the database, though. There's no way I'm gonna try and program around it!

Anyway, thanks for your help.

Gareth