Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Grouping Results By Period For Dates

Posted on 2013-05-20
9
Medium Priority
?
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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
Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

 
LVL 49

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
 

Author Comment

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

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

Expert Comment

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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