Aggregating data with a self-joined table in SQL Server 2008r2

I'm trying to provide reporting functionality on a typical restaurant type database. I describe the specifics of the problem below, but in a nutshell I need to be able retrieve aggregate data (sums and counts) for items that relate to a heirarchical self-joining "Category" table. I know that's wordy and probably confusing, so I'll try to relay the details with an example. I have four tables specific to this problem:

Categories
Id    Name          ParentId
1     Food          NULL
2     Drinks        NULL
3     Beer          2
4     Draft Beer    3
5     Bottle Beer   4
6     Pizza         1
8     Sandwiches    1

ParentId is a FK back to Categories

MenuItems
Id    Name              CategoryId  
1     6" S开发者_如何学Pythonausage        6
2     French Dip        8
3     Dogfish 60 IPA    4
4     Dogfish 60 IPA    5
5     Bud Light         5

Orders
Id    Total   DateReceived    DateCompleted
1     12.00   1/1/1970 1:00   1/1/1970 1:05 
2     11.50   1/1/1970 1:08   1/1/1970 1:18

OrderItems
Id    Price   OrderId   MenuItemId
1     9.00    1         1
2     3.00    1         5
3     3.50    2         3
4     8.00    2         2

The goal of this reporting functionality is to take a categoryId and return a sum (or count) over a given period of time. If the incoming category is a leaf category (e.g. Bottle Beer), I have no problem calculating this total. If however it's higher up the heirarchy, "Food" for instance, I can't figure out how to write the query in such a way that it will sum up all child categories.

I'm using SQL Server 2008r2. I tried using a WITH common_table_expression query, but I got an error about aggregate functions not allowed in the "recursive part of a recursive cte".

The SQL I have today looks something like this:

SELECT SUM(oi.Price) as Total, DAY(o.Completed)
FROM Orders o
JOIN OrderItems oi on oi.orderId = o.id
JOIN MenuItems mi on oi.MenuItemId = mi.id
JOIN Categories c on mi.CategoryId = c.id
WHERE o.Completed is not null
AND c.Id = @categoryId
AND o.Completed between @startDate and @endDate
GROUP BY DAY(o.completed)

Again, if @categoryId is a leaf category, this query gives me exactly what I'm looking for. If it's not, it returns nothing. I'd like to be able to provide @category=2 (Drinks) and get back the sum of all items anywhere in the "Drinks" category hierarchy.

Thanks!

Tobyb,
You're actually very close to the mark with trying to solve this problem with the CTE recursion method. Recursion is confusing at the best of times, but is the best solution for this type of problem.
I have copied the schema you have specified in your question and have come up with the following solution.

I have tested it and it produces the following output... I am assuming that this is what you want to see...

CategoryName CategoryId TotalSaleAmount
Food               1    17.00
Drinks             2     6.50
Beer               3     6.50
Draft Beer         4     3.50
Bottle Beer        5     3.00
Pizza              6     9.00
Sandwiches         8     8.00

Obviously the $17 for food is made up of pizza and sandwiches. Likewise the $6.50 for the drinks is made of the $6.50 for the beer, which in turn is made up of the $3.50 and $3.00 from the draft and bottled beer respectively.... etc etc...
This matches the hierarchy of the categories.

The code is below, this should work with your schema. If you wish to restrict the output add a where clause immediate before the group by.

WITH CategorySearch(ParentId, Id) AS
(
SELECT ID AS ParentId, ID
FROM Categories
WHERE ID NOT IN (SELECT ParentId FROM Categories 
                WHERE ParentId IS NOT NULL)
UNION ALL
SELECT Categories.ParentId, CS.Id
    FROM Categories Categories INNER JOIN CategorySearch CS
    ON Categories.Id = CS.ParentId
    WHERE Categories.ParentId IS NOT NULL
)
SELECT CA.Name AS CategoryName, CS.ParentId AS CategoryID, SUM(OI.Price) AS TotalSaleAmount
FROM Categories CA INNER JOIN CategorySearch CS
    ON CS.ParentId = CA.ID
INNER JOIN MenuItems MI
    ON MI.CategoryId = CS.Id
INNER JOIN OrderItems OI
    ON OI.MenuItemId = MI.ID
INNER JOIN Orders O
    ON OI.OrderId = O.Id
GROUP BY CA.Name, CS.ParentId
ORDER BY CS.ParentId

Have you tried using a recursive CTE to determine the category IDs of interest, and then joining on those results when doing your aggregation? Something like this I think:

WITH CatR AS
(
    SELECT Id FROM Categories WHERE Id = @CategoryId
    UNION ALL
    SELECT Categories.Id 
    FROM Cat
        INNER JOIN Categories ON Categories.ParentId = Cat.Id
)
SELECT SUM(Price)
FROM Orders
    INNER JOIN OrderItems ON OrderItems.OrderId = Orders.Id
    INNER JOIN MenuItems ON MenuItems.Id = OrderItems.MenuItemId
    INNER JOIN CatR ON CatR.Id = MenuItems.CategoryId
WHERE Orders.DateCompleted BETWEEN @StartDate AND @EndDate

I would change the database. The problem is you have two different classes of items in the same table.

Put Food and Drink in a CategoryType table. Leave the rest in the Category table. Join 'em.

Or, to avoid another table, add a category type field to the Category table. Type can be Food or Drink. You can use "Food" and "Drink" or 'F' and 'D'. Purists may not like this but the advantages are it is simpler and will run faster.