Forum Discussion
robertrackl
Nov 29, 2021Copper Contributor
Multi-Level Group By with Rollup
I need help with subtotals and totals. I have been studying and trying, but I have not been able to get it right. This is a portion of an accounting reporting scheme for our little glider flying club. Here is the structure of the table on which to report:
/****** Object: Table [dbo].[SF_REPTACCTS] Script Date: 2021/11/28 15:16:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SF_REPTACCTS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PiTRecordEntered] [datetime2](3) NULL,
[iRecordEnteredBy] [int] NULL,
[sCode] [nvarchar](25) NOT NULL,
[sSortCode] [nvarchar](40) NULL,
[sRAName] [nvarchar](256) NOT NULL,
[dAmount] [decimal](12, 2) NOT NULL,
[iParentAcct] [int] NULL,
[sNotes] [nvarchar](max) NULL,
CONSTRAINT [PK_SF_REPTACCTS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[SF_REPTACCTS] ADD CONSTRAINT [DF_SF_REPTACCTS_dAmount] DEFAULT ((0)) FOR [dAmount]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the Reporting Account' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SF_REPTACCTS', @level2type=N'COLUMN',@level2name=N'sRAName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Dollar figure in this account' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SF_REPTACCTS', @level2type=N'COLUMN',@level2name=N'dAmount'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'One account in this table is the Root account; its iParentAcct is zero. All other reporting accounts have iParentAcct > 0.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SF_REPTACCTS', @level2type=N'COLUMN',@level2name=N'iParentAcct'
GO
I wrote the following table-valued function to produce a hierarchical list of accounts from above table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF (OBJECT_ID('sf_ReptAcctsList') IS NOT NULL) DROP FUNCTION sf_ReptAcctsList
GO
CREATE FUNCTION sf_ReptAcctsList (@cReportType nchar(1))
RETURNS
@tblReptAccts TABLE
(
ID int, sCode nvarchar(25), sSortCode nvarchar(40), sRAName nvarchar(256),
dAmount decimal(12,2), sNotes nvarchar(MAX), iParentAcct int
)
AS
BEGIN
WITH CTE_DownLine(ID, sCode, sSortCode, sRAName, dAmount, AccountLevel,
sNotes, iParentAcct) AS
(
( -- Anchor
SELECT DISTINCT
R.ID, R.sCode, R.sSortCode,
CONVERT(varchar(255), R.sRAName) AS sCName,
R.dAmount, 1, R.sNotes, R.iParentAcct
FROM
SF_REPTACCTS AS R INNER JOIN SF_ACCTS_BRIDGE B ON B.iReportingAccount=R.ID
WHERE
iParentAcct = 1 AND B.cReportType = @cReportType
)
UNION ALL
( -- Recursive Part
SELECT
This.ID, This.sCode, This.sSortCode,
CONVERT(varchar(255), IIF(AccountLevel < 2,'|-- ','+-- ')
+ REPLICATE('--- ', AccountLevel-1) + This.sRAName) AS sCName,
This.dAmount, AccountLevel + 1, This.sNotes, This.iParentAcct
FROM
SF_REPTACCTS AS This
INNER JOIN CTE_DownLine AS Prior ON Prior.ID = This.iParentAcct
)
)
INSERT INTO @tblReptAccts (ID, sCode, sSortCode, sRAName, dAmount, sNotes, iParentAcct)
SELECT
ID, sCode, sSortCode, sRAName, dAmount, sNotes, iParentAcct
FROM
CTE_DownLine
RETURN
END
GO
When I run the command SELECT * FROM sf_ReptAcctsList('A') ORDER BY sSortCode then I get:
ID sCode sSortCode sRAName dAmount sNotes iParentAcct
5 4 04 Revenue 125.61 NULL 1
6 4.1 04.01 |-- Membership Dues/Fees 30.29 NULL 5
7 4.2 04.02 |-- SSA Membership Fees 63.46 NULL 5
8 4.3 04.03 |-- Tow Fees 155.78 NULL 5
9 4.4 04.04 |-- Glider Rental 92.99 NULL 5
10 4.5 04.05 |-- Miscellaneous Revenue 166.45 Assessments, Donations 5
2 5 05 Expenses 61.25 Expenses 1
3 5.1 05.01 |-- Facilities - Fixed 189.07 NULL 2
29 5.1.1 05.01.01 +-- --- Airfield Lease 30.43 NULL 3
30 5.1.2 05.01.02 +-- --- Airfield Liability Insurance 173.20 NULL 3
31 5.1.3 05.01.03 +-- --- Airfield Maintenance 163.06 NULL 3
32 5.1.4 05.01.04 +-- --- SSA Membership Fees 191.83 NULL 3
33 5.1.5 05.01.05 +-- --- Utilities 25.27 NULL 3
34 5.1.6 05.01.06 +-- --- Other Fixed Expenses 84.80 NULL 3
4 5.2 05.02 |-- Aircraft - Fixed 25.48 NULL 2
35 5.2.1 05.02.01 +-- --- Aircraft Insurance 124.14 NULL 4
36 5.2.2 05.02.02 +-- --- Hangaring/Tiedown 176.90 NULL 4
37 5.2.3 05.02.03 +-- --- Annual Inspections 90.75 NULL 4
38 5.2.4 05.02.04 +-- --- Aircraft Loan Interest 131.71 NULL 4
39 5.2.5 05.02.05 +-- --- Aircraft Licenses & Registrations 161.55 NULL 4
40 5.3 05.03 |-- Variable Expenses 143.31 NULL 2
41 5.3.1 05.03.01 +-- --- Fuel and Oil 153.31 NULL 40
42 5.3.2 05.03.02 +-- --- Repair & Maintenance 7.30 NULL 40
43 5.4 05.04 |-- Bad Debts 59.00 NULL 2
Now I wish to add total and subtotal rows presumably by using GROUP BY and ROLLUP, but I have not been able to figure out how, to achieve this output:
sCode sRAName dAmount sNotes
4 Revenue 125.61 NULL
4.1 |-- Membership Dues/Fees 30.29 NULL
4.2 |-- SSA Membership Fees 63.46 NULL
4.3 |-- Tow Fees 155.78 NULL
4.4 |-- Glider Rental 92.99 NULL
4.5 |-- Miscellaneous Revenue 166.45 Assessments, Donations
Total Revenue 634.58
5 Expenses 61.25 Expenses
5.1 |-- Facilities - Fixed 189.07 NULL
5.1.1 +-- --- Airfield Lease 30.43 NULL
5.1.2 +-- --- Airfield Liability Insurance 173.20 NULL
5.1.3 +-- --- Airfield Maintenance 163.06 NULL
5.1.4 +-- --- SSA Membership Fees 191.83 NULL
5.1.5 +-- --- Utilities 25.27 NULL
5.1.6 +-- --- Other Fixed Expenses 84.80 NULL
|-- Total Facilities - Fixed 857.66
5.2 |-- Aircraft - Fixed 25.48 NULL
5.2.1 +-- --- Aircraft Insurance 124.14 NULL
5.2.2 +-- --- Hangaring/Tiedown 176.90 NULL
5.2.3 +-- --- Annual Inspections 90.75 NULL
5.2.4 +-- --- Aircraft Loan Interest 131.71 NULL
5.2.5 +-- --- Aircraft Licenses & Registrations 161.55 NULL
|-- Total Aircraft - Fixed 710.53
5.3 |-- Variable Expenses 143.31 NULL
5.3.1 +-- --- Fuel and Oil 153.31 NULL
5.3.2 +-- --- Repair & Maintenance 7.30 NULL
|-- Total Variable Expenses 303.92
5.4 |-- Bad Debts 59.00 NULL
|-- Total Bad Debts 59.00
Total Expenses 1931.11
I am not sure whether the last step should happen inside the above function, or in a separate stored procedure. Your help in writing the appropriate SQL code is greatly appreciated! This sample shows three levels of accounts and subaccounts; it would be desirable if the code could handle any number of levels in the account hierarchy.
Thanks so much!
r4
No RepliesBe the first to reply