Setting a variable to a query

SET @mcount = (SELECT COUNT(Matter)FROM [tblCP_Matter]);

Select allOpen in new window

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);

Select allOpen in new window