SQL Count by last 12 months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Count by last 12 months

Author

Topic

ALSZ37
Starting Member

25 Posts

Posted - 2015-03-09 : 15:34:51

Hi All,

I understand how to count, but not sure how to approach this one. So I have 2 tables, referral & centers and I want to do a count by the last 12 months. So I want to display each center as row and then in each column go back 12 months with counts for referrals for the center. Any suggestions or help to accomplish would be great!

referral.center = center.id

Referrals
accountID Center Referral Date
1 23 2/1/2015
2 16 2/14/2015
3 15 12/13/2014
4 33 10/14/2014
5 41 11/16/2014

Center
id name
23 Center 23
16 center 16
15 center 15
33 center 33
41 center 41

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-10 : 07:05:57

[code]
WITH RefCounts
AS
(
SELECT Center, COUNT(*) AS RefCount
FROM Referrals
WHERE ReferralDate >= DATEADD(year, -1, CURRENT_TIMESTAMP)
GROUP BY Center
)
SELECT *
FROM Center C
LEFT JOIN RefCounts R
ON C.id = R.Center;
[/code]

ALSZ37
Starting Member

25 Posts

Posted - 2015-03-10 : 09:37:39

quote:

Originally posted by Ifor


WITH RefCounts
AS
(
	SELECT Center, COUNT(*) AS RefCount
	FROM Referrals
	WHERE ReferralDate >= DATEADD(year, -1, CURRENT_TIMESTAMP)
	GROUP BY Center
)
SELECT *
FROM Center C
	LEFT JOIN RefCounts R
		ON C.id = R.Center;

Thank you Ifor! Where does this go in my SQL? I have never used a "WITH" statement before.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-11 : 07:42:55

Prefix with ; It is a common table expression

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources