DATE STATISTICAL

  • Thread starter Thread starter Alex Hammerstein
  • Start date Start date
A

Alex Hammerstein

Hi

In a table of student records I have a start date and and end date.

Using the 01/04 - 31/03 as a year, I need to analyse by each year, the total
number of weeks the students have been enrolled and available.

So is a student started on 01/04 and left the following 31/03 -they would
have been available 52 weeks.

If a student enrolled on 01/05 and left on 01/06, they would have been
available for 4 weeks.

If a student enrolled on 01/05 and had not left, in year1 they would have
been available for 48 weeks and in would also be counted in year 2.

Is this possible in a query please?

Thanks

Alex
 
It is possible, if you use full date (with a year part).



Assuming the period of reference is defined as PeriodStart ( 01/04/200x )
to PeriodEnd (31/03/200x+1 )

Assuming the records have StuStart to StuEnd


Then, consider the common overlap is from MAX(StuStart, PeriodStart) to
MIN(StuEnd, PeriodEnd), for those records where the first expression occur
before the second expression. You can use DateDiff to get the number of
week boundaries between the two expressions:




SELECT StudentID,
SUM( DateDiff( "ww",
iif( StuStart > PeriodStart, StuStart, PeriodStart ),
iif( StuEnd < PeriodEnd, StuEnd, PeriodEnd))
)

FROM tableNameHere

WHERE iif( StuStart > PeriodStart, StuStart, PeriodStart )
< iif( StuEnd < PeriodEnd, StuEnd, PeriodEnd)

GROUP BY StudentID





Note that I used SUM in case the same student ID has two, or more, sequences
of presence. It is also required to have an aggregate there ( or to copy the
expression to the GROUP BY clause)




Vanderghast, Access MVP
 
Back
Top