Solved

SQL Server Grouping Results By Period For Dates

Posted on 2013-05-20
9
413 Views
Last Modified: 2013-05-29
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.
0
Comment
Question by:Karl_mark
9 Comments
 
LVL 8

Expert Comment

by:jpgobert
ID: 39180226
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
 

Expert Comment

by:SimonNFU
ID: 39180236
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39180252
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39180284
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:Karl_mark
ID: 39180329
Thanks all. Will give it a go and report back!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39180331
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39180349
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
 

Author Closing Comment

by:Karl_mark
ID: 39203760
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39203769
excellent, glad it helped.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now