DROP TABLE IF EXISTS Orders
DROP TABLE IF EXISTS OrderItems
GO
CREATE TABLE Orders ( row_id INT, parent_id INT, group_name VARCHAR(50) )
CREATE TABLE OrderItems ( order_id INT, price MONEY )
GO
INSERT Orders VALUES (1, NULL, 'test1'),
(2, NULL, 'test2'),
(3, 1, 'test1-3' ),
(4, 3, 'test1-3-4'),
(7, 3, 'test1-3-7'),
(5, 2, 'test2-5' ),
(6, 2, 'test2-6' ),
(8, 2, 'test2-8' ),
(9, 7, 'test1-3-7-9' )
GO
INSERT OrderItems VALUES (1, 11),
(2, 12),
(3, 13),
(4, 14),
(7, 15),
(5, 16),
(6, 17),
(8, 18),
(9, 19)
GO
CREATE OR ALTER FUNCTION dbo.OrderSum (@ORD INT) RETURNS MONEY
AS BEGIN
DECLARE @SUMM MONEY;
WITH calculate_cte(row_id, parent_id, group_name) AS (
SELECT row_id, parent_id, group_name FROM Orders WHERE row_id = @ORD
UNION ALL
SELECT Orders.row_id, Orders.parent_id, Orders.group_name
FROM Orders
INNER JOIN calculate_cte ON calculate_cte.row_id = Orders.parent_id
)
SELECT @SUMM = SUM(price) FROM calculate_cte
INNER JOIN OrderItems ON calculate_cte.row_id = OrderItems.order_id
RETURN @SUMM
END
GO
SELECT dbo.OrderSum(1), dbo.OrderSum(2), dbo.OrderSum(3), dbo.OrderSum(4)
