Trouble selecting Data between 2 dates in 2 diff Tables in SQL Server

[inAwww]

Hello all,

I am wondering if any of you might know what I am doing wrong in my situation. I am Using SQL server 2000
I am trying to count all the records in one table that fall between the date in that one table and a date in a second table. Is that possible to do?
so I have something like this

select count(Active)
from tblstudent, tblprofessor
where tblStudent.Startdate between tblStudent.Startdate and tblProfessor.EndDate

I have tried using a Join but I am not sure if I am doing something wrong too. Also the one error message I get relates to Active being ambiguous. (it does exist on both tables although one of them does not really need the active field)

Any help I would truly appreciate.
Thank you,

 

[tlbroadbent]

What is the relationship between the two tables? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[inAwww]

Well, I got rid of Active in one of the tables (the one that really does not need it) and it does count now but.. it takes into account all the dates in one of the tables. Is there a way to just make SQL take into account the very last record of a table or the last 2 records? and then compare it... Actually, after looking at this, it seems that what I need is something different such as

select count(Active)
from tblstudent, tblprofessor
where tblStudent.Startdate between tblProfessor.EndDate( record before last)and tblProfessor.EndDate(last Record)
If that is EVEN possible to do in SQL.

tlbroadbent, the relationship they have is made through another table although it does not look like it is a cross reference table. it is a look up table to which both tables reference. It is not too clear what the relationship is but it should be 1 to many.

 

[tlbroadbent]

It appears that you may not care about any relationship except the dates. You might be able to use a query like the following if that is correct. I assume that the last row on tblProfessor has the greatest EndDate.

Select ActiveCnt=Count(*)
From tblstudent
Where Startdate >=
(Select Max(EndDate) From tblprofessor
Where EndDate<
(Select Max(EndDAte) From tblprofessor))
And <= (Select Max(EndDAte) From tblprofessor) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[inAwww]

THank you, tlbroadbent. Your example totally set my mind to what to look for and helped me figure out what to do to get the data I need.