asked on

SQL Server Grouping Results By Period For Dates

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:

ID: 1
TypeWarning: A
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.

John Gobert

If it were me I'd start by defining the code that will evaluate the date and, instead of returning the date, return a value that represents the "half-term". Once you have that, then you can easily group on that value to get a count of warnings.

I deal with something similar at my job sometimes... What I usually do is define a "period" by creating a varchar(6) value made up of the year and month (YYYYMM). If i'm working quarterly then that changes to the year and quarter as Q1, Q2, Q3 or Q4.

I'm guessing you want this to be something that will work going forward no matter if the dates for the periods change? If so, you may need to keep a variable table in your DB that you can keep updated with the dates for each period... that way your query will have an active source for proper dates and values going forward.

Does this make sense or am I on the wrong track?

SimonNFU

Hi Karl

Not sure if I am understanding you question fully so apologise in advance if this is not the answer you are looking for

on the assumption you can filter by year you should be able to knock something up with this logic

Select Warning where warningdate between Autumn Term 1 Start and
Half Term Start or between Autumn Term 2 Start and Autumn Term 2 End etc

Hope that's a good starter for you

Kind Regards
Simon

PortletPaul

some sample data would be a big help here, but I don't think it's too hard to achieve.

Your 'expected results' would also help us design a query to best suit you, but here is an idea that may suit:

select
  type_warning
, count(case when warning_date >= yrdates.half_tem_start and warning_date < yrdates.half_term_end then 1 end) as warnings_1st_half
, count(case when warning_date >= yrdates.half_tem_start and warning_date < yrdates.half_term_end then 1 end) as warnings_2nd_half
-- more like this for other date ranges
from warnings
cross join (
             select half_tem_start, half_term_end from academic_year
             where ac_year = 2012
             /* when using cross join: this MUST return just one row, and not be null! */
           ) as yrdates
group by type_warning

Open in new window

nb: cross join may not be the only approach, it is just an example.

You will also see here that as I don't know what the table names of fields are I've just used my imagination for those. If you would like more specific code please provide the tables/fields names along with sample data.

PortletPaul

think that example needs a bit of improvement :)

select
  type_warning
, count(case when warning_date >= yrdates.half_term_one_start and warning_date < yrdates.half_term_one_end then 1 end) as warnings_1st_half
, count(case when warning_date >= yrdates.half_term_two_start and warning_date < yrdates.half_term_two_end then 1 end) as warnings_2nd_half
, count(case when warning_date >= yrdates.term_one_start and warning_date      < yrdates.term_one_end      then 1 end) as warnings_term_one
, count(case when warning_date >= yrdates.term_two_start and warning_date      < yrdates.term_two_end      then 1 end) as warnings_tern_two
-- more like this for other date ranges
from warnings
cross join (
             select
                 half_term_start
               , half_term_end 
               , term_one_start
               , term_one_end
               , term_two_start
               , term_two_end
               /* more fields like this */
             from academic_year
             where ac_year = 2012
             /* IF using a cross join approach: this MUST return just one row, and not be null! */
           ) as yrdates
 group by
  type_warning

Open in new window

Karl_mark

ASKER

Thanks all. Will give it a go and report back!

PortletPaul

there is more...

I'm wondering if transposing that academic year record into multiple rows might make it easier overall. Working on a sample.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial