View Poll Results: What do you think about this question?

Voters
0. You may not vote on this poll
  • Like it

    0 0%
  • Dislike it

    0 0%
  • Meh!

    0 0%
  • Is irrelevant

    0 0%
Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: How to query acedemic year dates?

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The most efficient way to write it is to ask someone on a forum to write it for you

    How efficiently this runs depends on the indexing. Without an appropriate clustered or covering index it will cause a table scan. If there are a lot of columns in the table unused by the query then the appropriate clustered index would perform poorly compared to the covering index.

    Does the datetime column contain any time component, or is it date only?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, in terms of the query I would probably use a calendar table. There are other parts to academic years that you might want one day.
    Code:
    CREATE TABLE academic_years
    ( academic_year INT, year_start DATETIME, year_end DATETIME, autumn_half_Term_start DATETIME etc..)
    Then:
    Code:
    SELECT academic_year, col1, col2
    FROM myTable
    INNER JOIN 
    academic_years
    ON myTable.the_date BETWEEN academic_years.year_start AND academic_years.year_end
    WHERE academic_years.academic_year BETWEEN 2003 AND 2008
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •