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.
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 |
 |
|
|
|
|