Count Occurances between dates

[Ripper]

I have a frontend linked to a SQL 2000 database.

I have a table (tblIssIn) that contains student assignments to ISS,
IssInID(Auto#), LocID, StartDate, EndDate.

I want to create a query or nest of, that count the number of students in
ISS and for 40 days from today's date.

I have a table ,tblDaysAdvance, that I use in a query to create a list of
dates from today to 40+ days from today.

How do I use the StartDate and EndDate to count the students in ISS by
without getting the dreaded error about aggegate functions and group by.
#1820 I think. I was successfully using the following sql query, but I get
the error when I try to use it in SQl. Anyone have any tips?

SELECT qryDaysAdvance.TheDate, Count(*) AS NumOf
FROM tblIssIn, qryDaysAdvance
WHERE (((qryDaysAdvance.TheDate) Between [StartDate] And [EndDate]))
GROUP BY qryDaysAdvance.TheDate
ORDER BY qryDaysAdvance.TheDate;

 

[Lord Kelvan]

ummm technally is it impossible to have data 40 days from today unless
you are entering records that havent happened yet i am guessing you
want to see in 40 days which students are going to be handing in
assignments in is the only logical data i can think of you cannot do
that query on the start and end date because they will give differnet
meaning of the query

select locid,count([enddate])
from tblissin
group by locid
having enddate between date() and dateadd("d",40,date())

and i am guessing the locid is the student but that will show how many
assigns per student if thats the case

select count([enddate])
from tblissin
having enddate between date() and dateadd("d",40,date())

that will just give you a number for how many students will hand in
assignements for a period of now to 40 days from now... as long as i
understand what you are trying to do.

i supose if you are trying to find out how many students are doing or
have done (cannot define the difference [above query defineds how many
have done]) assignments you could do

select count([startdate])
from tblissin
having startdate between date() and dateadd("d",40,date())

if these arnt what you want please define the table more clearly ie
what each field means and exatally what you are trying to achieve

Regards
Kelvan

 

[Ken Sheridan]

Rip:

Try this:

SELECT TheDate,
(SELECT COUNT(*)
FROM tblIssIn
WHERE tblDaysAdvance.The Date
BETWEEN StartDate AND EndDate)
AS NumberOfStudents
FROM tblDaysAdvance
WHERE TheDate BETWEEN DATE() AND DATE()+40
ORDER BY TheDate

The subquery is correlated with the outer query on the date in the latter's
current row falling between the start and end dates in the tblIssIn table.

Ken Sheridan
Stafford, England