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.
Karl_markAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
here is a quite different approach, still based on many assumptions, but it may enable multi-year analysis as well as single year (e.g. are we improving year on year? 5 year trends etc)
declare @acyear as int
set @acyear = 2008
  
;with
acdates as (
            select
               ac_year
            from academic_year
            cross apply (
                      values 
                             ('term 1',term_one_start, term_one_end)
                           , ('term 2',term_two_start, term_two_end)
                           , ('term 3',term_three_start, term_three_end)
                           , ('half 1',half_one_start, half_one_end)
                           , ('half 2',half_two_start, half_two_end)
                        )  AS CrossApplied(term_code, start, end)
            where ac_year > @acyear
           ) 
select
  acdates.ac_year
, warnings.type_warning
, acdates.term_code
, count(warnings.warning_date) as warning_count
from warnings
inner join acdates on warnings.warning_date >= acdates.start and warnings.warning_date < acdates.end 
group by
  acdates.ac_year
, warnings.type_warning
, acdates.term_code

Open in new window

{+ edit, some typos}
0
 
jpgobertEnterprise IT Systems ConsultantCommented:
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?
0
 
SimonNFUCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
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.
0
 
PortletPaulfreelancerCommented:
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

0
 
Karl_markAuthor Commented:
Thanks all. Will give it a go and report back!
0
 
PortletPaulfreelancerCommented:
there is more...

I'm wondering if transposing that academic year record into multiple rows might make it easier overall.  Working on a sample.
0
 
Karl_markAuthor Commented:
Thanks again portletpaul. Sorry for the delay; busy time here in the college with exams!  I've done some tweaking to fit the fields and it works.
0
 
PortletPaulfreelancerCommented:
excellent, glad it helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.