Query Involving Dates 1

[DawsonUK]

Hi,

I'm currently working on a database to hold records of students. Each student may take many suspensions from their studies for a length of time.

I have 2 tables which are of relevance to this problem, students and suspensions. Below is the bare minimum attributes which are needed to make the links work.

Students...
Student ID
Student Name

Suspensions...
Suspension ID
Student ID
Study End
Study Recommence

The problem I'm having with this is trying to display who is currently an active student (not on a suspension) and those which are currently on a suspension of studies.

I have a query which shows all students which are on a suspension complete, and it works fine. I can't however get a query working to show those who are not currently on a suspension of studies. Basically, the query needs to show students who have no suspensions at all, and those where todays date does not lie between Study end and Study Recommence.

Some sample data to help....
Full Name | Study End | Study Recommence
David Daw 12/1/1998 12/3/2000
David Daw 16/4/2000 16/8/2000
David Daw 7/6/2003 7/10/2003
Lesley Ch 12/2/2003 12/11/2003


The problem seems to be that the query that I have right now is that my query discards records where the suspension is current, which is correct, but still displays past and future suspensions that are not current.

Thanks in advance for any help, this has completely baffled me and my collegue!

David Dawson

 

[DanChard]

Is the following something like what you're after?

select student_name
from students
where student_id not in (select student_id
from suspensions
where now() between study_end and study_recommence);

If not, please post again and point out where this isn't correct.

Good luck!

 

[DawsonUK]

Hello,

Thanks, that works perfectly! Thanks very much for your help!


David

 

[SonOfEmidec1100]

You may have a small problem when the recommence date is today. Now() includes the time this means it could be greater the the recommence date, depending on the time part of recommence date.

If your checks are to ignore times and your table dates have a time of 0:0:0 then use

WHERE format(NOW(),"dd-mmm-yyyy&quot between study_end and study_recommence

Otherwise

WHERE format(NOW(),"dd-mmm-yyyy&quot between format(study_end,"dd-mmm-yyyy&quot AND format(study_recommence,"dd-mmm-yyyy&quot

Could be a better way to do, does this make sense ?

 

[DawsonUK]

Hi, I think that makes sense.
The dates are stored in short date format, say dd/mm/yy.

what about using date() instead of now()?

Its not too important if the students due to return on that day are on the list to be honest. All this query is used for is to show students who are on a suspension. On my search screen the users have the ability to view "Active" students, or "Inactive" students, the inactive ones being the students on a suspenension of studies.

Another query has been setup to show students and the amount of time before they recommence their studies. This other query is used for contacting them about returning to their studies.


Thanks,
David