How to group by two data fields in a common table expression

Question

I need to compare the final balances of two tables from different clients grouped by ID. The tables have the same ID's But one has multiple ID entries and the other does not. I need to sum row from the table with multiple entries so I have just one final number to do calculations with.

I have the query that does this much, but now I need to get it to group by month as well. My current query totals it for all dates. The data actually needs to be sorted by ID and by month so they know the ending monthly balance for every ID. Is there a way to do this with what I have so far?

Thanks in advance.

This is what I have so far.

    -- Data setup
    CREATE TABLE [Table1]
    (
        [ID] INT,
        [cost] INT,
        [traceNumber] INT,
        [TheDate] DATE
    )

    INSERT [Table1]
    VALUES  (1, 200, 1001, '9/07/2011'),
            (1, -20, 1002, '9/08/2011'),
            (1, 130, 1003, '10/10/2011'),
            (2, 300, 1005, '10/10/2011')

    CREATE TABLE [Table2]
    (
        [ID] INT,
        [cost] INT
     )

     INSERT [Table2]
    VALUES  (1, 200),
            (2, 300)

    -- Query
    ;WITH [cteTable1Sum] AS
    (
        SELECT [ID], SUM([cost]) AS [cost]
        FROM [Table1]
        GROUP BY [ID]
    )       
    SELECT  [Table1].[ID], 
            [Table1].[TheDate], 
            [Table1].[traceNumber],
            [Table1].[cost] AS [Frost_Balance],
            cte.[cost] AS [SUM_Frost_Balance],
            [Table2].[cost] AS [Ternean_Balance],
            cte.[cost] - [Table2].[cost] AS [Ending_Balance]
    FROM [Table1]
    INNER JOIN [Table2]
        ON [Table1].[ID] = [Table2].[ID]
    INNER JOIN [cteTable1Sum] cte
         ON [Table1].[ID] = cte.[ID]

I tried this and I get the wrong answer.

    WITH [cteTable1Sum] AS 
         ( SELECT [ID], SUM([cost]) 
         AS [cost] FROM [Table1] 
         GROUP BY [ID], [TheDate] )

Answer 1

You should probably use the MONTH() and YEAR() functions for this:

;WITH [cteTable1Sum] AS
    (
        SELECT [ID],
              MONTH(TheDate)  as Mo,
              YEAR(TheDate) as yr,
              SUM([cost]) AS [cost]
        FROM [Table1]
        GROUP BY [ID], MONTH(TheDate), YEAR(TheDate)
    )       

This will give you the monthly breakdown of each month for each ID. Then change your JOIN to:

INNER JOIN [cteTable1Sum] cte
         ON [Table1].[ID] = cte.[ID]
         AND MONTH(Table1.TheDate) = cte.Mo
         AND YEAR(Table1.TheDate) = cte.Yr