October 31, 2012 at 2:06 pm
I've a SQL query that returns the TotalMemberCount for the past 5 years on a yearly basis.
Now I want to have a new column as "Year-Over-Year Growth"
Here is sample query and the resultset......
SELECT Years = DATENAME(year, EnrollDate),
COUNT(MemberID) as TotalMembers
FROM dbo.Enrollment
WHERE EnrollDate BETWEEN '1/1/2008' AND '10/25/2012'
GROUP BY DATENAME(year, EnrollDate)
ORDER BY DATENAME(year, EnrollDate);
Years:
2008
2009
2010
2011
2012
MemberCount:
10000
13000
12500
14000
16000
In the above example, I need a new "YOY Growth" column that should look like,
Year-Over-Year Growth:
0
3000
-500
1500
2000
Here the 1st row of growth data should always be 0 based on the selection of the date range.
Thanks for your help in advance.
October 31, 2012 at 2:43 pm
This should work:
WITH YOYCTE ([Year], [TotalMembers])
AS
(
SELECT Years = DATENAME(year, EnrollDate),
COUNT(MemberID) as TotalMembers
FROM dbo.Enrollment
WHERE EnrollDate BETWEEN '1/1/2008' AND '10/25/2012'
GROUP BY DATENAME(year, EnrollDate)
)
SELECT curyr.[Year], curyr.[TotalMembers],
ISNULL(curyr.[TotalMembers] - prevyr.[TotalMembers], 0) AS 'Year-Over-Year Growth'
FROM YOYCTE curyr
LEFT JOIN YOYCTE prevyr on curyr.[Year] = prevyr.[Year] + 1
ORDER BY curyr.[Year]
October 31, 2012 at 3:17 pm
Thanks Charles. That's exactly what I needed 🙂
October 31, 2012 at 7:07 pm
This should also work:
;WITH YOY AS (
SELECT Years = DATENAME(year, EnrollDate)
,COUNT(MemberID) as TotalMembers
FROM dbo.Enrollment
WHERE EnrollDate BETWEEN '1/1/2008' AND '10/25/2012'
GROUP BY DATENAME(year, EnrollDate)
ORDER BY DATENAME(year, EnrollDate))
SELECT Years, TotalMembers
,YOYG=ISNULL(TotalMembers - (
SELECT TOP 1 TotalMembers
FROM YOY b
WHERE a.Years > b.Years
ORDER BY Years DESC), 0)
FROM YOY a
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply