Geek asks and answers

Aggregating data using a native table in SQL Server 2008r2

I am trying to provide reporting functions in a typical restaurant type database. I describe the specifics of the problem below, but in a nutshell I need to be able to retrieve aggregated data (sums and counts) for items related to the hierarchical self-joining Category table. I know this is verbose and probably confusing, so I will try to convey 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 returns FK in category

MenuItems
Id    Name              CategoryId  
1     6" Sausage        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

- categoryId ( ) . - (, ), . , , heirarchy, , "Food", , , .

SQL Server 2008r2. WITH common_table_expression, , " cte".

SQL, , :

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)

, @categoryId , , . , . @category = 2 () "".

!

+3
3

Tobyb,
, CTE. , .
, , .

, ... , , ...

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

, $17 . , 6,50 $ 6,50 , , , 3,50 3,00 .... .....
.

, . , where .

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
+2

CTE , ? - :

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
0

. , .

CategoryType. Category. .

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

-1
source

More articles:


All Articles