set @tiYear = 2002 set @tiFiscalMonth = 6 SELECT co1.specdesc,AdmitDesc, (select COUNT(dbo.meetings.meetingnumber) from dbo.meetings where datepart(yy,begintime)= @tiYear and meetings.specID = co1.specID and meetings.AdmitTypeID = m1.AdmitTypeID and iscancelled = 0) as YTD FROM dbo.ctAdmissionType ad INNER JOIN dbo.meetings m1 ON ad.AdmitTypeID = m1.AdmitTypeID INNER JOIN dbo.cospec co1 ON m1.specid = co1.specid where m1.iscancelled=0 group by ad.AdmitDesc,m1.admitTypeID,co1.specdesc,co1.specID order by 1