DATE STATISTICAL

  • Thread starter Alex Hammerstein
  • 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
 
M

Michel Walsh

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top