merging two stored procedures

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.

Help will be great! :)

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 < @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 <= @BeginDate
                                AND C.CaseId NOT IN (SELECT CaseId FROM CaseStatusChange CSC WHERE CSC.DateClosed < @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 <= @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

If I understood you right, you need something like :

CREATE PROCEDURE new_proc
AS
BEGIN
     DECLARE @tmp_proc1 TABLE (// list all fields your first procedure returns );
     DECLARE @tmp_proc2 TABLE (// list fields that your second SP returns);
     INSERT INTO @tmp_proc1 
      EXECUTE Your_First_Procedure ;
     INSERT INTO @tmp_proc2
      EXECUTE Your_Second_Procedure;

     // Finally, join data in @tmp_proc1 and @tmp_proc2 
     //(you probably need FULL JOIN) and return 1 resultset

END;

Provided you adjust the header to add all parameters needed, you can add as many result sets to a stored procedure as you need. Consuming the multiple tables will vary based on the language and platform you are using to consume the data, but simply result set from one sproc into the other should work fine, unless there is a limitation in the underlying data store (database server?).

ADDED: Based on your response, you can combined the two results sets in a single stored procedure. To consume this, you have a variety of options. With a Reader, you can go to next result set, as it is a firehose cursor. But, it might be easier to use something like a DataSet and have it generated form the stored procedure. you can then use a table adapter to fill the data set from the stored procedure. Both tables should be filled now with a single call.

Does this make sense?