query week in the last year?

  • Hopefully this is somewhat easy, any help is greatly appreciated. I have a table with a date, stored as a week:

    2008-02-03 00:00:00.000

    2008-01-27 00:00:00.000

    2008-01-20 00:00:00.000

    etc

    How can I query this field to find all rows in a rolling 12 month or 4 quarters period?

    Thanks!

  • What does the date represent, the end of the reporting period? How does it equate to the start of the period? Using the dates in your original post, what would the starting dates of the periods be?

  • thanks for the reply. The dates should all be the first day of the week, a Sunday. That's how we start our reporting period for each week.

    2008-12-14 00:00:00.000

    2008-12-21 00:00:00.000

    2008-12-28 00:00:00.000

    So I'm trying to write a query where this date is within the last 12 rolling months.

    Thanks!

  • If I understand what you're asking for then I think what you need is to add the code below to your where clause:

    DATEDIFF(month, YourDateColumn, getdate()) <= 12



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Still confused. You need to provide a better description of the problem and what you are trying to accomplish. Some sample tables (DDL to create), sample data (in a readily consumable format), expected results based on the sample data would really help.

    Need help with this and why you should do it that way, read this article, Forum Etiquette: How to post data/code on a forum to get the best help[/url]; and this blog, The Flip Side.

  • awesome! Thank you so much, that was exactly what I was looking for.

  • Alvin, congrats on figuring out what was needed. Of course, it won't make use of any indexes that may benefit the query if they exist. 😉

  • Lynn Pettis (5/6/2009)


    Alvin, congrats on figuring out what was needed. Of course, it won't make use of any indexes that may benefit the query if they exist. 😉

    Would using "between" use indexes?

    Another option would be to assign the start date to a variable, like

    DECLARE @StartDate as datetime

    SET @StartDate = dateadd(month, -12, getdate())

    then in the where clause

    WHERE TableDate between @StartDate and Getdate()



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • That's why I asked for more info. I didn't really feel comfortable giving an answer with so little info to really go on.

  • sorry about that, I should have given more info. It's actually for another application, which is handling the indexing, so it's not an issue. Thanks again!

  • ericb1 (5/6/2009)


    sorry about that, I should have given more info. It's actually for another application, which is handling the indexing, so it's not an issue. Thanks again!

    What?!?!?!?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Alvin Ramard (5/6/2009)


    ericb1 (5/6/2009)


    sorry about that, I should have given more info. It's actually for another application, which is handling the indexing, so it's not an issue. Thanks again!

    What?!?!?!?

    Ditto...

  • I have an ASPXGridView in VisualStudio (http://www.devexpress.com/Products/NET/Controls/ASP/Grid/)

    This control builds grids, which is based off a view I have in SQL2k5. I needed to add a WHERE clause to narrow down the rows being returned from my view.

    So the gridview is binding to the datasource for me, I just needed to provide the where clause.

  • ericb1 (5/6/2009)


    I have an ASPXGridView in VisualStudio (http://www.devexpress.com/Products/NET/Controls/ASP/Grid/)

    This control builds grids, which is based off a view I have in SQL2k5. I needed to add a WHERE clause to narrow down the rows being returned from my view.

    So the gridview is binding to the datasource for me, I just needed to provide the where clause.

    Which most likely means you now have an inefficient query hitting the database. If it can't use an index, it usually means a clustered index/table scan. That means it has to look at all the data to determine what to return. Small tables, not too bad. Very large tables, a killer for performance.

    It would help if you could provide the query as well as the underlying tables in the view.

  • ericb1 (5/6/2009)


    sorry about that, I should have given more info. It's actually for another application, which is handling the indexing, so it's not an issue.

    Indexing as in indexes on the database tables to make searches against the tables efficient so they don't cause massive locking and very slow queries. No app can provide that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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