Multi-Level Group By with Rollup

Copper Contributor

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 &amp; 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 &amp; 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 &amp; 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 &amp; 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 

 

 

0 Replies