How to query on acedemic years?

Question

Post reply

How to query on acedemic years?

mcommmgr

SSC Rookie

Points: 47
More actions
January 25, 2010 at 7:38 pm

#216080

I have a standard datetime field value. I need to write a query that looks aggregating numbers by acedemic year. This acedemic year is defined by August 1st thought July 31st.
I need to report on the past 5 acedemic years.
Any tips on the most efficient way to write this in a SQL statement?

BUSINESS @ THE SPEED OF THOUGHT
J. Joshi
Harvard University Medical Informatics
Cambridge, USA.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

I suggest a second table to hold the academic year and the starting date of that year. ie.

declare @AcademicYears TABLE (AcademicYear smallint PRIMARY KEY CLUSTERED, StartDate datetime)

insert into @AcademicYears

values (2010, '20090801'),

(2009, '20080801'),

(2008, '20070801'),

(2007, '20060801'),

(2006, '20050801')

Now just join to this table to get the academic year...

select ay.AcademicYear, <your columns>, <your aggregrated columns>

from <your table> yt

INNER JOIN @AcademicYears ay

ON yt.MyDateField >= ay.StartDate

AND yt.MyDateField < DateAdd(year,1,ay.StartDate)

where <your where clause>

group by ay.AcademicYear, <your columns>

Alternately, you can add an EndDate column to the @AcademicYears table and avoid the DateAdd function call.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

mcommmgr (1/25/2010)
I have a standard datetime field value. I need to write a query that looks aggregating numbers by acedemic year. This acedemic year is defined by August 1st thought July 31st.
I need to report on the past 5 acedemic years.
Any tips on the most efficient way to write this in a SQL statement?

While I agree with the idea of using a table to define the years, it does mean that someone will need to remember to change the table once a year.

With that in mind, it would probably be good to have the system figure out which years to report on base on the current date. And, with that in mind, which years would you like to report on for the date of 15 Jan 2010?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mcommmgr SSC Rookie Points: 47 More actions · Answer 3

I don't want this to be a table driven query for teh same reason you describe - I need to remember every year to go and update the values in a table. I prefer running a query based off of date fields directly.

BUSINESS @ THE SPEED OF THOUGHT
J. Joshi
Harvard University Medical Informatics
Cambridge, USA.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

mcommmgr (1/25/2010)
I have a standard datetime field value. I need to write a query that looks aggregating numbers by acedemic year. This acedemic year is defined by August 1st thought July 31st.
I need to report on the past 5 acedemic years.
Any tips on the most efficient way to write this in a SQL statement?

So, based on today (2010-02-01), you would want the current academic year (2009-08-01 - 2010-07-31) plus the previous 5 academic years (2008-08-01 - 2009-07-31, 2007-08-01 - 2008-07-31, 2006-07-01 - 2007-07-31, 2005-08-01 - 2006-07-31, 2004-08-01 - 2005-07-31), correct?

mcommmgr SSC Rookie Points: 47 More actions · Answer 5

That is correct. And going forward too of course. I am imagining when we hot 8/1/2010, my academic year would now be 1011 instead of 0910. So on and so forth.

BUSINESS @ THE SPEED OF THOUGHT
J. Joshi
Harvard University Medical Informatics
Cambridge, USA.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

mcommmgr (2/1/2010)
That is correct. And going forward too of course. I am imagining when we hot 8/1/2010, my academic year would now be 1011 instead of 0910. So on and so forth.

Okay, having a real problem with the sample code I wrote posting here this time, so uploading it as an attachment.

Here is some sample code.

mcommmgr SSC Rookie Points: 47 More actions · Answer 7

Working on it!

BUSINESS @ THE SPEED OF THOUGHT
J. Joshi
Harvard University Medical Informatics
Cambridge, USA.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

mcommmgr (2/1/2010)
I don't want this to be a table driven query for teh same reason you describe - I need to remember every year to go and update the values in a table. I prefer running a query based off of date fields directly.

So answer my previous question. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 9

Heh... never mind. I see that Lynn got an answer to his question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 10

Isn't the academic year just YEAR(DATEADD(MONTH, -7, {date_time}))?

USE tempdb;

CREATE TABLE #Data

(

date_time DATETIME NOT NULL,

value INTEGER NOT NULL,

academic_year AS

YEAR(DATEADD(MONTH, -7, date_time))

PERSISTED NOT NULL

);

WITH Numbers (n)

AS (

SELECT TOP (5500)

ROW_NUMBER() OVER (ORDER BY C1.object_id, C1.column_id)

FROM master.sys.columns C1,

master.sys.columns C2,

master.sys.columns C3

)

INSERT #Data

(date_time, value)

SELECT DATEADD(DAY, Numbers.N, '19950101'),

Numbers.n

FROM Numbers;

SELECT *

FROM #Data;

DROP TABLE #Data;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Paul White (2/1/2010)
Isn't the academic year just YEAR(DATEADD(MONTH, -7, {date_time}))?
USE tempdb;
CREATE TABLE #Data
(
date_time DATETIME NOT NULL,
value INTEGER NOT NULL,
academic_year AS
YEAR(DATEADD(MONTH, -7, date_time))
PERSISTED NOT NULL
);
WITH Numbers (n)
AS (
SELECT TOP (5500)
ROW_NUMBER() OVER (ORDER BY C1.object_id, C1.column_id)
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3
)
INSERT #Data
(date_time, value)
SELECT DATEADD(DAY, Numbers.N, '19950101'),
Numbers.n
FROM Numbers;
SELECT *
FROM #Data;
DROP TABLE #Data;

It really depends on the organization. For us (a K12 Public School District) the 2009/2010 school year is 2010, the year that the Seniors graduate. However, in one of our third party systems, it is 2009. So even there we are not consistant, but we had to go with how the vedors software works.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

mcommmgr (2/1/2010)
Working on it!

Okay, you've had a couple of days to work on this, what's happening?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 13

Lynn Pettis (2/3/2010)
mcommmgr (2/1/2010)
Working on it!
Okay, you've had a couple of days to work on this, what's happening?

I'm curious too to see more info on this

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mcommmgr SSC Rookie Points: 47 More actions · Answer 14

I went with a simple table route rather than trying to identify values on the fly. There were some strange errors around December and January months who was throwing the equation off. I just populate dthe next 20 years of academic years.

If in 20 years, this SQL query is still alive - then the organization is in a little bit of a problem! 🙂

BUSINESS @ THE SPEED OF THOUGHT
J. Joshi
Harvard University Medical Informatics
Cambridge, USA.