May 6, 2009 at 10:27 am
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!
May 6, 2009 at 10:55 am
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?
May 6, 2009 at 11:14 am
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!
May 6, 2009 at 11:28 am
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
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]
May 6, 2009 at 11:29 am
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.
May 6, 2009 at 11:37 am
awesome! Thank you so much, that was exactly what I was looking for.
May 6, 2009 at 11:40 am
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. 😉
May 6, 2009 at 11:48 am
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()
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]
May 6, 2009 at 12:00 pm
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.
May 6, 2009 at 3:00 pm
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!
May 6, 2009 at 3:08 pm
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?!?!?!?
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]
May 6, 2009 at 3:13 pm
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...
May 6, 2009 at 3:19 pm
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.
May 6, 2009 at 3:23 pm
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.
May 6, 2009 at 3:30 pm
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply