HOW TO QUERY ACADEMIC YEARS? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HOW TO QUERY ACADEMIC YEARS?

Author

Topic

JJ910
Starting Member

1 Post

Posted - 2010-01-25 : 21:34:21

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 23:55:59

do you have a calendar table?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-01-26 : 07:43:00

You probably need to create a mapping table with a "name" for your schoolyear (i.e "2009/2010") and then add two datetime columns with the start and end of the school year:

CREATE TABLE schoolyear (
  YearName varchar(200),
  Starting datetime,
  Ending datetime
)

INSERT INTO schoolyear SELECT '2009/2010', '2009-08-01 00:00:00.000', '2010-07-31 23:59:59.999' 

SELECT YearName, SUM(somenumber)
FROM table a
  INNER JOIN schoolyear b
    ON a.DateTimeColumn BETWEEN b.Starting AND b.Ending
GROUP BY YearName

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-26 : 09:13:18

Cant this be done by just shifting all the dates in 1 direction? Like Year(DateAdd(M,-8,myDateField)), so anything up to (as an example) Aug 31 2010 will roll to 2009. (IE the 2009 Academic year)

Or go the other way and add months if you want it to push forward.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-01-26 : 15:49:31

Something like this? This puts July 2004 in the 2004 schoolyear but August will be in year 2005

Jim


CASE WHEN DATEPART(month,aDate) < 8 
      THEN YEAR(aDate) 
      ELSE YEAR(aDate) + 1
 END

Everyday I learn something that somebody else already knew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 15:53:59

[code]DECLARE @Sample TABLE
(
Data DATETIME
)

INSERT @Sample
SELECT '20080703' UNION ALL
SELECT '20080803' UNION ALL
SELECT '20080903' UNION ALL
SELECT '20081003' UNION ALL
SELECT '20081103' UNION ALL
SELECT '20081203' UNION ALL
SELECT '20090103' UNION ALL
SELECT '20090203' UNION ALL
SELECT '20090303' UNION ALL
SELECT '20090403' UNION ALL
SELECT '20090503' UNION ALL
SELECT '20090603' UNION ALL
SELECT '20090703' UNION ALL
SELECT '20090803'

SELECT Data,
DATEPART(YEAR, DATEADD(MONTH, -7, Data)) AS AcademicYearBackwards,
DATEPART(YEAR, DATEADD(MONTH, 5, Data)) AS AcademicYearForwards
FROM @Sample
ORDER BY Data[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources