The Last Three Months (Year and Month Only)
-
I have a summary table that stores values by Year and Month. I would like to create a report that shows the last three months only. For example, January 2006 should give me Nov and Dec 2005. The problem is that I can't seem to isolate the year. I was wondering if someone has already created a UDF or SP that does this.
Here is what I have so far...problem is that I get January for 2006 and 2005 when I just want 2006. Thanks for any input!!!
SELECT [Year],[Month],SUM(ViewCount) AS Downloads
FROM FileViews
WHERE [Month] = Month(DATEADD(Month,-2,GETDATE())))
OR ([Month] = Month(DATEADD(Month,-1,GETDATE())))
OR ([Month] = Month(GETDATE())))
AND [Year] >= Year(GETDATE())
GROUP BY [Year], [Month]
-
DECLARE @months int
SET @months = (YEAR(GETDATE()) * 12) + MONTH(GETDATE())
SELECT [Year],[Month],SUM(ViewCount) AS Downloads
FROM FileViews
WHERE ([Year] * 12) + [Month] BETWEEN @months-2 AND @months
GROUP BY [Year], [Month]
Far away is close at hand in the images of elsewhere.
Anon. -
You rock. Thank you very much for sharing this innovative approach. Works perfectly.
-
Steve, I ran your code, and other than a few extra parentheses, yours gives the correct results too.
-
My suggestion would be the following. If you have an index on (Year, Month), I think it is faster than David's suggestion.
SELECT F.[Year], F.[Month], SUM(F.ViewCount) AS Downloads
FROM FileViews F inner join
(
select month(dateadd(m, -2, getdate())) as m, year(dateadd(m, -2, getdate())) as y
union all
select month(dateadd(m, -1, getdate())), year(dateadd(m, -1, getdate()))
union all
select month(getdate()), year(getdate())
)
M
on F.[Year] = M.y and F.[Month] = M.m
GROUP BY F.[Year], F.[Month]
-
Another interesting approach!
I ran both queries against a million-row table to compare the execution plan. David's suggestion showed a subtree cost of 7. Yours originally had a subtree cost of 9. I added a clustered index on Year and Month (as you suggested) and the subtree cost dropped to 4. Even though the graphical execution plan looked much more complex, the overall performance was better.
Thanks for the input!
Steve
-
How many posts do I need before I can get rid of that NEWBIE moniker?!!
-
I don't remember if it's 10 or 20...
Instead of joining on a derived table (the one with alias M above), you might want to join on a (temp) table and pre-insert all desired year-month combinations. This way you won't have to rewrite (the last part of) your query if you want to output data from time periods of a different length or with a different starting point.
-
I was thinking about your comments and how to create a UDF that looks at GETDATE and say an argument for the number of months when it occured to me that I was overanalyzing the problem...this gives me the same result that I am looking for with a lower query cost.
SELECT TOP 3 [Year],[Month],SUM(ViewCount) AS Downloads
FROM FileViews
GROUP BY [Year], [Month]
ORDER BY [Year] DESC,[Month] DESC
I realize that a more complex query would need to filter in the WHERE clause, but this works for what I was trying to do. Dooh!
-
That's definitely simpler!
And congratulations - you are now a Grashopper
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply