Link to home
Create AccountLog in
Avatar of Jacque Scott
Jacque ScottFlag for United States of America

asked on

Setting a variable to a query

I have a stored procedure where I am setting variables to queries.  This one works.
SET @mcount = (SELECT COUNT(Matter)FROM [tblCP_Matter]);

Open in new window



Why won't this one work with a CTE?
SET @Period_mcount = (WITH cte AS 
(
SELECT DISTINCT cp_matter.MatterId FROM [tblCP_ActivityLog] cp_Act 
JOIN [tblCP_AccountMatter]  cp_AM ON cp_Act.SourceObjectKey = cp_AM.Account 
JOIN [tblCP_Matter] cp_matter ON cp_matter.Matter = cp_AM.Matter 
WHERE ActionType = 'Create Account' AND MONTH(CAST(ActivityDate AS DateTime)) = '04'  AND YEAR(CAST(ActivityDate AS DateTime)) = '2016'
) 
SELECT count(*) FROM cte);

Open in new window

SOLUTION
Avatar of Haris Dulic
Haris Dulic
Flag of Austria image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Jacque Scott

ASKER

Those both worked.  Thank you for the quick response.