SQL-запрос для получения сумм элементов в иерархической структуре между двумя таблицами

Question

SQL-запрос для получения сумм элементов в иерархической структуре между двумя таблицами

Приведена таблица T_BudgetCharts. Он содержит иерархическую структуру элементов от 1-го до n-го уровня. Значение присваивается только элементам на N-м уровне. Другие элементы, которые не являются последним уровнем, имеют 0 присваиваний.

Приведена другая таблица, T_Invoice. Эта таблица содержит некоторые заказы, которые находятся в отношении к первой таблице, используя столбец BudgetItemID.

Что мне нужно, так это написать SQL-запрос, который возвращает TotalSum каждого BudgetItem из T_BudgetCharts - включая сумму на каждом уровне, зарезервированную сумму для каждого элемента в таблице T_Invoice - (здесь возникает проблема) - включая зарезервированную сумму на каждом уровне в herarchy и сумму разницы, которая является разницей между первыми двумя значениями.

Я работаю над SQLSERVER 2008R2...

Прилагается изображение для лучшего понимания. Также SQL дамп...

CREATE TABLE [dbo].[T_BudgetCharts](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [BudgetId] [int] NOT NULL,

    [BudgetItemLevel] [int] NOT NULL,

    [BudgetItemCode] [nvarchar](20) NOT NULL,

    [BudgetItemName] [nvarchar](300) NOT NULL,

    [BudgetItemMasterID] [int] NULL,

    [BudgetItemBudegtValue] [decimal](18, 2) NULL,

    [BugedtIdOriginal] [int] NULL,

    [MasterIdOriginal] [int] NULL,

    [BugedtItemStatus] [int] NOT NULL,

    [sysDateTimeCreated] [datetime] NOT NULL,

    [sysDateTimeModified] [datetime] NOT NULL,

     CONSTRAINT [PK_T_BudgetCharts] PRIMARY KEY CLUSTERED 

    (

        [ID] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]



SET IDENTITY_INSERT [dbo].[T_BudgetCharts] ON

INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (1, 1, 1, N'N/A', N'Not assigned', NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CA5499 AS DateTime), CAST(0x0000A68000CA5499 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (2, 1, 2, N'N/A', N'Not assigned', 1, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CA5499 AS DateTime), CAST(0x0000A68000CA5499 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (3, 1, 3, N'N/A', N'Not assigned', 2, CAST(1000.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CA5499 AS DateTime), CAST(0x0000A68000CA7067 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (4, 2, 1, N'N/A', N'Not assigned', NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CA6213 AS DateTime), CAST(0x0000A68000CA6213 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (5, 2, 2, N'N/A', N'Not assigned', 4, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CA6213 AS DateTime), CAST(0x0000A68000CA6213 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (6, 2, 3, N'N/A', N'Not assigned', 5, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CA6213 AS DateTime), CAST(0x0000A68000CA6213 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (7, 1, 1, N'A', N'Category A', NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CA95BA AS DateTime), CAST(0x0000A68000CA95BA AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (8, 1, 2, N'100', N'Income', 7, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CAAEAF AS DateTime), CAST(0x0000A68000CAAEAF AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (9, 1, 2, N'200', N'Outcome', 7, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CAB852 AS DateTime), CAST(0x0000A68000CAB852 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (10, 1, 3, N'A.100.1', N'Income 1', 8, CAST(2500.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CADF5F AS DateTime), CAST(0x0000A68000CB0545 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (11, 1, 3, N'A.100.2', N'Income 2', 8, CAST(10000.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CAF466 AS DateTime), CAST(0x0000A68000CAF466 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (13, 1, 3, N'A.200.1', N'Outcome 1', 9, CAST(6000.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CBB48C AS DateTime), CAST(0x0000A68000CBB48C AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (14, 1, 3, N'A.200.2', N'Other stuff', 9, CAST(3000.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CBC7EE AS DateTime), CAST(0x0000A68000CBC7EE AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (15, 2, 1, N'T1', N'Money', NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CC2709 AS DateTime), CAST(0x0000A68000CC2709 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (16, 2, 2, N'99', N'Type A', 15, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CC3E2C AS DateTime), CAST(0x0000A68000CC3E2C AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (17, 2, 2, N'98', N'Type B', 15, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CC4E48 AS DateTime), CAST(0x0000A68000CC4E48 AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (18, 2, 3, N'T1.98.1', N'B 1', 17, CAST(300.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CC698D AS DateTime), CAST(0x0000A68000CC698D AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (19, 2, 3, N'T1.98.2', N'B 2', 17, CAST(800.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CC73DB AS DateTime), CAST(0x0000A68000CC73DB AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (20, 2, 3, N'T1.99.1', N'A 1', 16, CAST(600.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CC958F AS DateTime), CAST(0x0000A68000CC958F AS DateTime))

    INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (21, 2, 3, N'T1.99.2', N'A 2', 16, CAST(400.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A68000CCA173 AS DateTime), CAST(0x0000A68000CCA173 AS DateTime))

    SET IDENTITY_INSERT [dbo].[T_BudgetCharts] OFF



CREATE TABLE [dbo].[T_Invoice](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [InvoiceBudgetingID] [int] NOT NULL,

    [OrderNo] [int] NOT NULL,

    [BudgetID] [int] NOT NULL,

    [BudgetItemID] [int] NOT NULL,

    [AmountProjectCurrency] [decimal](28, 16) NOT NULL,

    [AmountBudgetCurrency] [decimal](28, 16) NOT NULL,

    [BudgetCurrencyRate] [decimal](28, 16) NOT NULL,

    [Percentage] [decimal](28, 16) NOT NULL,

 CONSTRAINT [PK_T_Invoice] PRIMARY KEY CLUSTERED 

(

    [ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]





SET IDENTITY_INSERT [dbo].[T_Invoice] ON

INSERT [dbo].[T_Invoice] ([ID], [InvoiceBudgetingID], [OrderNo], [BudgetID], [BudgetItemID], [AmountProjectCurrency], [AmountBudgetCurrency], [BudgetCurrencyRate], [Percentage]) VALUES (1, 1, 1, 1, 10, CAST(300.0000000000000000 AS Decimal(28, 16)), CAST(300.0000000000000000 AS Decimal(28, 16)), CAST(1.0000000000000000 AS Decimal(28, 16)), CAST(100.0000000000000000 AS Decimal(28, 16)))

INSERT [dbo].[T_Invoice] ([ID], [InvoiceBudgetingID], [OrderNo], [BudgetID], [BudgetItemID], [AmountProjectCurrency], [AmountBudgetCurrency], [BudgetCurrencyRate], [Percentage]) VALUES (2, 2, 1, 1, 14, CAST(200.0000000000000000 AS Decimal(28, 16)), CAST(200.0000000000000000 AS Decimal(28, 16)), CAST(1.0000000000000000 AS Decimal(28, 16)), CAST(100.0000000000000000 AS Decimal(28, 16)))

INSERT [dbo].[T_Invoice] ([ID], [InvoiceBudgetingID], [OrderNo], [BudgetID], [BudgetItemID], [AmountProjectCurrency], [AmountBudgetCurrency], [BudgetCurrencyRate], [Percentage]) VALUES (3, 2, 1, 1, 14, CAST(500.0000000000000000 AS Decimal(28, 16)), CAST(100.0000000000000000 AS Decimal(28, 16)), CAST(1.0000000000000000 AS Decimal(28, 16)), CAST(100.0000000000000000 AS Decimal(28, 16)))

SET IDENTITY_INSERT [dbo].[T_Invoice] OFF

**

CREATE TABLE [dbo].[T_Bookings](

        [ID] [int] IDENTITY(1,1) NOT NULL,

        [BookingTypeID] [int] NOT NULL,

        [ProjectID] [int] NOT NULL,

        [InvoiceDate] [date] NOT NULL

     CONSTRAINT [PK_T_Bookings] PRIMARY KEY CLUSTERED 

    (

        [ID] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    /****** Object:  Table [dbo].[T_Bookings]    Script Date: 09/21/2016 15:06:23 ******/

    SET IDENTITY_INSERT [dbo].[T_Bookings] ON

    INSERT [dbo].[T_Bookings] ([ID], [BookingTypeID], [ProjectID], [InvoiceDate]) VALUES (1, 1, 1, CAST(0xCF3B0B00 AS Date))

    INSERT [dbo].[T_Bookings] ([ID], [BookingTypeID], [ProjectID], [InvoiceDate]) VALUES (2, 2, 1, CAST(0xCF3B0B00 AS Date))

    INSERT [dbo].[T_Bookings] ([ID], [BookingTypeID], [ProjectID], [InvoiceDate]) VALUES (3, 1, 1, CAST(0xCF3B0B00 AS Date))

    SET IDENTITY_INSERT [dbo].[T_Bookings] OFF

**

Пока у меня есть первая часть, но не могу получить забронированная сумма и разница...Вот мой текущий запрос...Надеюсь, кто-нибудь сможет помочь...

WITH C AS 

(

    SELECT  b.Id,

            b.BudgetItemMasterID,

            b.BudgetItemCode,

            b.BudgetItemName,

            b.BudgetItemBudegtValue,

            b.BugedtItemStatus,

            b.Id AS RootID

    FROM T_BudgetCharts b

    WHERE b.BugedtItemStatus = 1

    UNION ALL

    SELECT  b.Id,

            b.BudgetItemMasterID,

            b.BudgetItemCode,

            b.BudgetItemName,

            b.BudgetItemBudegtValue,

            b.BugedtItemStatus,

            C.RootID

    FROM T_BudgetCharts b

    INNER JOIN C ON b.BudgetItemMasterID = C.Id

    WHERE b.BugedtItemStatus = 1

)



SELECT  b.ID as ID,

        b.BudgetItemMasterID AS MasterID,

        b.BudgetItemCode AS BudgetItemCode,

        b.BudgetItemName AS BudgetItemName,

        s.TotalValue AS TotalValue,

        ISNULL(g.Spent, 0) as BookedSum,

        0 as DifferenceSum --Not implemnted yet..

FROM T_BudgetCharts b

INNER JOIN (SELECT RootID, SUM(BudgetItemBudegtValue) AS TotalValue FROM C GROUP BY RootID) AS s ON b.Id = s.RootID

LEFT JOIN (SELECT BudgetItemId, SUM(AmountProjectCurrency) AS Spent FROM dbo.T_Invoice GRoup By BudgetItemId) AS g ON b.ID = g.BudgetItemID

ORDER BY b.BudgetItemCode

456 1

sql-server sum common-table-expression

1 ответ:

Comments

Ничего не найдено.

SlimsGhost · Accepted Answer · 2016-09-20 22:29:58

EDIT: исправлено условие соединения и некоторые другие детали (извините!)

Следующий CTE соединяет каждый Id как "корень" дерева, содержащего себя и всех потомков со всех уровней). Запрос с помощью CTE свертывает все, что вы хотите, что основано на группе (идентификатор и все его дочерние элементы).
Я попытался сделать его похожим на вашу фотографию, но, возможно, Вам все равно придется подогнать его под то, что вам нужно. Ключевой частью является использование CTE для определения каждой "группы" (каждый родитель и все своих детей).
WITH C AS 
(
    SELECT  b.Id AS RootId,
            b.BudgetItemMasterID as RootBudgetItemMasterID,
            b.BudgetItemCode as RootBudgetItemCode,
            b.BudgetItemName as RootBudgetItemName,
            b.Id as ChildId
    FROM T_BudgetCharts b
    WHERE b.BugedtItemStatus = 1
    UNION ALL
    SELECT  c.RootId,
            c.RootBudgetItemMasterID,
            c.RootBudgetItemCode,
            c.RootBudgetItemName,
            b.Id as ChildId
    FROM T_BudgetCharts b
    INNER JOIN C ON b.BudgetItemMasterID = C.ChildId
    WHERE b.BugedtItemStatus = 1
)
SELECT 
    C.RootId as Id,
    C.RootBudgetItemMasterID as BudgetItemMasterID,
    C.RootBudgetItemCode as BudgetItemCode,
    C.RootBudgetItemName as BudgetItemName,
    sum(isnull(b.BudgetItemBudegtValue, 0)) AS TotalValue,
    sum(isnull(g.AmountProjectCurrency, 0)) AS BookedSum,
    sum(isnull(b.BudgetItemBudegtValue, 0)) - sum(isnull(g.AmountProjectCurrency, 0)) as DifferenceSum
from C 
inner join T_BudgetCharts b ON b.Id = C.ChildId and b.BugedtItemStatus = 1
left join T_Invoice g ON b.ID = g.BudgetItemID
group by C.RootId, C.RootBudgetItemMasterID, C.RootBudgetItemCode, C.RootBudgetItemName
order by C.RootId, C.RootBudgetItemMasterID, C.RootBudgetItemCode, C.RootBudgetItemName