SQL Server CTE for Aggregating Costs at Each Level of Hierarchy

Viewed 724

I can't figure out how to write a CTE that will roll up costs from a transaction table to a self-join table so that it will give me total costs at each level of the hierarchy. I've put together a very simple example to illustrate the problem. Here are the DDL and Insert scripts so you can reproduce the issue, should you be so kind as to help me out:

CREATE TABLE [Items](
    [ItemId] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NULL,
    [ItemName] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
    [ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Transactions](
    [TransactionId] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [int] NOT NULL,
    [Amount] [money] NOT NULL,
 CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED 
(
    [TransactionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [Items] ON 
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (1, NULL, N'Warehouse')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (3, 1, N'Bin 1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (4, 1, N'Bin 2')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (5, 3, N'Item 1.1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (6, 3, N'Item 1.2')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (7, 4, N'Item 2.1')
GO
INSERT [Items] ([ItemId], [ParentId], [ItemName]) VALUES (8, 4, N'Item 2.2')
GO
SET IDENTITY_INSERT [Items] OFF
GO
SET IDENTITY_INSERT [Transactions] ON 
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (1, 5, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (2, 5, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (3, 6, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (4, 6, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (5, 4, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (6, 7, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (7, 7, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (8, 8, 10.0000)
GO
INSERT [Transactions] ([TransactionId], [ItemId], [Amount]) VALUES (9, 8, 10.0000)
GO
SET IDENTITY_INSERT [Transactions] OFF
GO
ALTER TABLE [Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Items] FOREIGN KEY([ParentId])
REFERENCES [Items] ([ItemId])
GO
ALTER TABLE [Items] CHECK CONSTRAINT [FK_Items_Items]
GO
ALTER TABLE [Transactions]  WITH CHECK ADD  CONSTRAINT [FK_Transactions_Items] FOREIGN KEY([ItemId])
REFERENCES [Items] ([ItemId])
GO
ALTER TABLE [Transactions] CHECK CONSTRAINT [FK_Transactions_Items]
GO

Here is the CTE that I've been working on:

With cteAggregateCost
as  
(  
    select i.itemId, i.ParentId, t.Amount
    from Items i join Transactions t on i.ItemId = t.ItemId
union all
    select i.itemId, i.ParentId, t.Amount
    from Items i join cteAggregateCost c on i.ItemId = c.ParentId
    join Transactions t on i.ItemId = t.ItemId
)
select i.ParentId, i.ItemId, i.ItemName, sum(Amount) As AggregateCost
from Items i left join cteAggregateCost c on i.ItemId = c.ItemId
group by i.ParentId, i.ItemId, i.ItemName

This is the result I am getting:

Bad Result

And this is the result I am hoping to get:

Hoped-for Result

As you can see, all lines are working except the first two, that do not have a cost for the container, only their contained items.

Thanks so much for any direction you may be able to offer!

2 Answers
Related