sql-serversumcommon-table-expressionhierarchical
SQL Query to get item sums in hierarchical structure between two tables
A table T_BudgetCharts is given. It contains a hierarchical structure of items from 1 to Nth level. Only Items on the Nth level have a value assigned. The other items, which are not last level, have a 0 assigend.
Another table, T_Invoice is given. This table contains some bookings which are in the relation to the first table using the BudgetItemID column.
What i need is to write an SQL Query which returns TotalSum of each BudgetItem from T_BudgetCharts - including the sum on each Level, the Booked Sum for each item in the T_Invoice table - (here comes the problem) - including the booked sum on each level in the herarchy, and the difference sum, which is the difference between the first two values.
I am working on SQLSERVER 2008R2...
An image for better understanding is attached. Also SQL dump...
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
**
So far i have the first part, but cant get the booked sum and the difference...Here is my current query...Hope someone can help...
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
Solution
EDIT: fixed a join condition and a few other details (sorry!)
The following CTE connects each Id as the "root" of a tree containing itself and ALL children from ALL levels). The query using the CTE rolls up ANYTHING YOU WANT that is based on the group (the Id and all its children).
I tried to make it look just like your picture, but you might still need to adjust it to fit what you need. The key part is using the CTE to define each "group" (each parent and all of its children).
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
- SQLdevelopersfruit puzzle
- Bacpac vs dacpac for migrating database to Azure
- Return total records from SQL Server when using ROW_NUMBER
- SSIS Error: External Column for Source out of sync with Data Source columns; How do I remove External Columns?
- Caching paged LINQ results without using Skip() or Take()
- Paging results from a complex SQL Server query
- does snapshot isolation level protects from Phantom read?
- Sql Server ldf files growing very fast
- T-SQL SELECT DISTINCT & ROW_NUMBER() OVER Ordering Problem
- What's the best method to do paging in my ASP page
- What is the Equivalent syntax of mysql " LIMIT " clause in SQL Server
- How to use STRING_AGG function inside another STRING_AGG function in SQL Server
- Is there any performance issue using Row_Number to implement table paging in Sql Server 2008?
- Clean up a SQL Server Query with too many DISTINCT clauses in it
- SQLAlchemy does not connect to SQL Server with ODBC Driver 18 for SQL Server but pyodbc does
- Sort Month Name in SQL Query
- When should I use semicolons in SQL Server?
- Import Excel to Pandas Dataframe, but preserve order of Day/Month in dates?
- Encryption PowerShell script generated by SQL Server Management Studio executes but doesn't work
- I need good tool for database unit testing
- how to calculate database transaction count per second and database growth
- SQL Server TRY...CATCH Is Not Catching An Error
- JDBC connection hangs with no response from SQL Server 2008 r2
- How to use Table -Valued Parameter with Dynamic query
- Why won't SQL Server allow creation of a user with SQL Server Authentication?
- Last Run Date on a Stored Procedure in SQL Server
- SQL Server: Search all tables for a particular GUID
- Truncate (not round) decimal places in SQL Server
- Find a string by searching all tables in SQL Server
- What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?