title: merging two stored procedures
body: <p>So this is what i thought of doing but now the error i am getting is : Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause and not sure which part it means - the overall code i am trying to get open cases based on two different levels one is to return cases based on date range passed in and the other is to return cases based on just the begin date and before it. </p>
  
  <p>Help will be great! :)</p>
  
  <p>CODE: </p>
  
  <pre><code>    SELECT
         C.CaseNumber,
         O.OfficeName,
         CT.Description AS CaseType,
         DATEADD(dd, 0, DATEDIFF(dd, 0, C.DateOpened)) AS DateOpened,
         CR.Description AS Court,
         CaseOfficeAppointment.OpenCases,
         CaseOfficeAppointment.CloseCases
  FROM 
  (
         SELECT  C.CaseId, O.OfficeId, CRT.CourtId,
          (
                     SELECT COUNT(DISTINCT CD.CaseId)
                     FROM [Case] CD
                      INNER JOIN CaseOffice COD ON CD.CaseId = COD.CaseId
                      --INNER JOIN Court CR ON CD.CourtId = CR.CourtId
                      INNER JOIN Office OD ON COD.OfficeId = OD.OfficeId
                      LEFT OUTER JOIN CaseStatusChange CSC ON CD.CaseId = CSC.CaseId
                     --WHERE CR.CourtId = CRT.CourtId
                     WHERE OD.OfficeId = O.OfficeId
                     AND
                     ( CD.DateOpened BETWEEN @BeginDate AND @EndDate 
                          OR
                       CSC.DateReopened BETWEEN @BeginDate AND @EndDate
                      ) 
          )AS OpenCases,
          (
                     SELECT COUNT(DISTINCT CD.CaseId)
                     FROM [Case] CD
                      INNER JOIN CaseOffice COD ON CD.CaseId = COD.CaseId
                      --INNER JOIN Court CR ON CD.CourtId = CR.CourtId
                      INNER JOIN Office OD ON COD.OfficeId = OD.OfficeId
                      LEFT OUTER JOIN CaseStatusChange CSC ON CD.CaseId = CSC.CaseId
                     --WHERE CR.CourtId = CRT.CourtId
                     WHERE OD.OfficeId = O.OfficeId
                     AND
                     ( CSC.DateClosed BETWEEN @BeginDate AND @EndDate 
                      ) 
          )AS CloseCases
      FROM  [Case] C
          INNER JOIN [Appointment] A ON C.CaseId = A.CaseId
          INNER JOIN [Office] O ON A.OfficeId = O.OfficeId
          INNER JOIN [Court] CRT ON C.CourtId = CRT.CourtId
  
  
      WHERE 
             -- Case was open (or reopened) during the date range 
             C.DateOpened BETWEEN @beginDate AND @endDate 
             OR 
             C.CaseId IN (SELECT CaseId FROM CaseStatusChange WHERE DateReopened BETWEEN @beginDate AND @endDate)
             AND 
             -- Office had an appointment sometime during the date range
             A.DateOn &lt; @endDate AND (A.DateOff IS NULL OR A.DateOff BETWEEN @beginDate AND @endDate)
  
             GROUP BY C.CaseId, O.OfficeId, CRT.CourtId,
          (
                  SELECT  OfficeId, SUM(CaseCount)AS Counts
                  FROM    (
                              SELECT  COUNT(C.CaseId) AS CaseCount,O.OfficeId
                              FROM    [Case] C
                                  INNER JOIN [Appointment] A ON C.CaseId = A.CaseId
                                  INNER JOIN [Office] O ON A.OfficeId = O.OfficeId
                              WHERE   C.DateCreated &lt;= @BeginDate
                                  AND C.CaseId NOT IN (SELECT CaseId FROM CaseStatusChange CSC WHERE CSC.DateClosed &lt; @BeginDate)
                              --GROUP BY O.OfficeId
  
                              UNION  
  
                              -- Also need the cases that reopened and are currently open
                              SELECT  COUNT(ReOpened.CaseId) As CaseCount, ReOpened.OfficeID
                              FROM (
  
                                      SELECT C.CaseId, MAX(CSC.DateReopened) AS DateReOpened, O.OfficeId 
                                      FROM [Case] C 
                                      INNER JOIN [CaseStatusChange] CSC ON C.CaseId = CSC.CaseId
                                      INNER JOIN [Appointment] A ON C.CaseId = A.CaseId
                                      INNER JOIN [Office] O ON A.OfficeId = O.OfficeId
                                      WHERE CSC.DateReopened &lt;= @BeginDate
                                      --GROUP BY C.CaseId, O.OfficeID
                                  ) AS ReOpened 
                              WHERE ReOpened.CaseId NOT IN 
                                  (
                                      SELECT CaseId FROM CaseStatusChange 
                                      WHERE CaseId = ReOpened.CaseId AND 
                                      CaseStatusChange.DateClosed BETWEEN ReOpened.DateReopened AND @BeginDate
                                  )
                              GROUP BY ReOpened.OfficeId
                          ) AS OpenCasesCount
                  GROUP BY OfficeId
          )
  )
  CaseOfficeAppointment
  INNER JOIN [Case] C ON CaseOfficeAppointment.CaseId = C.CaseId
  INNER JOIN [Office] O ON CaseOfficeAppointment.OfficeId = O.OfficeId
  INNER JOIN [CaseType] CT ON C.CaseTypeId = CT.CaseTypeId
  INNER JOIN [Court] CR ON C.CourtId = CR.CourtId
  </code></pre>