I have a table in SQL Server which shows the key dates for an Academic Year. Example:
Autumn Term 1 Start: 01/09/2012
Half Term Start: 29/10/2012
Half Term End 02/11/2012
Autumn Term 2 Start: 05/11/2012
Autumn Term 2 End: 21/12/2012 etc.
Each of the values above is held in a different field, so that is the column headings with the values held as dates. There is a discrete record for each academic year. The table is mainly used to fire events throughout the academic year.
In another table, we have details of warnings given to students through the year. A typical record consists of:
Date: 2012/11/01 10:32:01.400
Notes: Failed to submit module 2 coursework
I need to do some analysis on when warnings were issued so I can compare whether or not there has been an increase over academic years and group the data by term dates. So, I need to show a total of warnings for each half-term period (in the example above, this would be between 01/09/2012 and 29/10/2012 as one value, and 05/11/2012 to 21/12/2012 as another value). I could hard-code these dates, but obviously the academic year changes each year due to Easter etc. so it's a laborious task.
Is there a way of taking the date that a warning was issued and grouping it by using the term dates table? I suspect that I could do this with a cross join, but I'm not sure exactly how this would work.