Multi-Level Group By with Rollup

%3CLINGO-SUB%20id%3D%22lingo-sub-3012571%22%20slang%3D%22en-US%22%3EMulti-Level%20Group%20By%20with%20Rollup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3012571%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20help%20with%20subtotals%20and%20totals.%20I%20have%20been%20studying%20and%20trying%2C%20but%20I%20have%20not%20been%20able%20to%20get%20it%20right.%20This%20is%20a%20portion%20of%20an%20accounting%20reporting%20scheme%20for%20our%20little%20glider%20flying%20club.%20Here%20is%20the%20structure%20of%20the%20table%20on%20which%20to%20report%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E%2F******%20Object%3A%20%20Table%20%5Bdbo%5D.%5BSF_REPTACCTS%5D%20%20%20%20Script%20Date%3A%202021%2F11%2F28%2015%3A16%3A06%20******%2F%0ASET%20ANSI_NULLS%20ON%0AGO%0A%0ASET%20QUOTED_IDENTIFIER%20ON%0AGO%0A%0ACREATE%20TABLE%20%5Bdbo%5D.%5BSF_REPTACCTS%5D(%0A%20%5BID%5D%20%5Bint%5D%20IDENTITY(1%2C1)%20NOT%20NULL%2C%0A%20%5BPiTRecordEntered%5D%20%5Bdatetime2%5D(3)%20NULL%2C%0A%20%5BiRecordEnteredBy%5D%20%5Bint%5D%20NULL%2C%0A%20%5BsCode%5D%20%5Bnvarchar%5D(25)%20NOT%20NULL%2C%0A%20%5BsSortCode%5D%20%5Bnvarchar%5D(40)%20NULL%2C%0A%20%5BsRAName%5D%20%5Bnvarchar%5D(256)%20NOT%20NULL%2C%0A%20%5BdAmount%5D%20%5Bdecimal%5D(12%2C%202)%20NOT%20NULL%2C%0A%20%5BiParentAcct%5D%20%5Bint%5D%20NULL%2C%0A%20%5BsNotes%5D%20%5Bnvarchar%5D(max)%20NULL%2C%0A%20CONSTRAINT%20%5BPK_SF_REPTACCTS%5D%20PRIMARY%20KEY%20CLUSTERED%20%0A(%0A%20%5BID%5D%20ASC%0A)WITH%20(PAD_INDEX%20%3D%20OFF%2C%20STATISTICS_NORECOMPUTE%20%3D%20OFF%2C%20IGNORE_DUP_KEY%20%3D%20OFF%2C%20ALLOW_ROW_LOCKS%20%3D%20ON%2C%20ALLOW_PAGE_LOCKS%20%3D%20ON%2C%20OPTIMIZE_FOR_SEQUENTIAL_KEY%20%3D%20OFF)%20ON%20%5BPRIMARY%5D%0A)%20ON%20%5BPRIMARY%5D%20TEXTIMAGE_ON%20%5BPRIMARY%5D%0AGO%0A%0AALTER%20TABLE%20%5Bdbo%5D.%5BSF_REPTACCTS%5D%20ADD%20%20CONSTRAINT%20%5BDF_SF_REPTACCTS_dAmount%5D%20%20DEFAULT%20((0))%20FOR%20%5BdAmount%5D%0AGO%0A%0AEXEC%20sys.sp_addextendedproperty%20%40name%3DN'MS_Description'%2C%20%40value%3DN'Name%20of%20the%20Reporting%20Account'%20%2C%20%40level0type%3DN'SCHEMA'%2C%40level0name%3DN'dbo'%2C%20%40level1type%3DN'TABLE'%2C%40level1name%3DN'SF_REPTACCTS'%2C%20%40level2type%3DN'COLUMN'%2C%40level2name%3DN'sRAName'%0AGO%0A%0AEXEC%20sys.sp_addextendedproperty%20%40name%3DN'MS_Description'%2C%20%40value%3DN'Dollar%20figure%20in%20this%20account'%20%2C%20%40level0type%3DN'SCHEMA'%2C%40level0name%3DN'dbo'%2C%20%40level1type%3DN'TABLE'%2C%40level1name%3DN'SF_REPTACCTS'%2C%20%40level2type%3DN'COLUMN'%2C%40level2name%3DN'dAmount'%0AGO%0A%0AEXEC%20sys.sp_addextendedproperty%20%40name%3DN'MS_Description'%2C%20%40value%3DN'One%20account%20in%20this%20table%20is%20the%20Root%20account%3B%20its%20iParentAcct%20is%20zero.%20All%20other%20reporting%20accounts%20have%20iParentAcct%20%26gt%3B%200.'%20%2C%20%40level0type%3DN'SCHEMA'%2C%40level0name%3DN'dbo'%2C%20%40level1type%3DN'TABLE'%2C%40level1name%3DN'SF_REPTACCTS'%2C%20%40level2type%3DN'COLUMN'%2C%40level2name%3DN'iParentAcct'%0AGO%0A%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wrote%20the%20following%20table-valued%20function%20to%20produce%20a%20hierarchical%20list%20of%20accounts%20from%20above%20table%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESET%20ANSI_NULLS%20ON%0AGO%0ASET%20QUOTED_IDENTIFIER%20ON%0AGO%0AIF%20(OBJECT_ID('sf_ReptAcctsList')%20IS%20NOT%20NULL)%20DROP%20FUNCTION%20sf_ReptAcctsList%0AGO%0ACREATE%20FUNCTION%20sf_ReptAcctsList%20(%40cReportType%20nchar(1))%0ARETURNS%20%0A%40tblReptAccts%20TABLE%20%0A(%0A%20ID%20int%2C%20sCode%20nvarchar(25)%2C%20sSortCode%20nvarchar(40)%2C%20sRAName%20nvarchar(256)%2C%0A%20%20dAmount%20decimal(12%2C2)%2C%20sNotes%20nvarchar(MAX)%2C%20iParentAcct%20int%0A)%0AAS%0ABEGIN%0A%20WITH%20CTE_DownLine(ID%2C%20sCode%2C%20sSortCode%2C%20sRAName%2C%20dAmount%2C%20AccountLevel%2C%0A%20%20sNotes%2C%20iParentAcct)%20AS%0A%20(%0A%20%20%20(%20--%20Anchor%0A%20%20%20%20SELECT%20DISTINCT%0A%20%20%20%20%20R.ID%2C%20R.sCode%2C%20R.sSortCode%2C%0A%20%20%20%20%20CONVERT(varchar(255)%2C%20R.sRAName)%20AS%20sCName%2C%0A%20%20%20%20%20R.dAmount%2C%201%2C%20R.sNotes%2C%20R.iParentAcct%0A%20%20%20%20FROM%0A%20%20%20%20%20SF_REPTACCTS%20AS%20R%20INNER%20JOIN%20SF_ACCTS_BRIDGE%20B%20ON%20B.iReportingAccount%3DR.ID%0A%20%20%20%20WHERE%0A%20%20%20%20%20iParentAcct%20%3D%201%20AND%20B.cReportType%20%3D%20%40cReportType%0A%20%20%20)%0A%20%20%20UNION%20ALL%0A%20%20%20(%20--%20Recursive%20Part%0A%20%20%20%20SELECT%0A%20%20%20%20%20This.ID%2C%20This.sCode%2C%20This.sSortCode%2C%0A%20%20%20%20%20CONVERT(varchar(255)%2C%20IIF(AccountLevel%20%26lt%3B%202%2C'%7C--%20'%2C'%2B--%20')%0A%20%20%20%20%20%20%2B%20REPLICATE('---%20'%2C%20AccountLevel-1)%20%2B%20This.sRAName)%20AS%20sCName%2C%0A%20%20%20%20%20This.dAmount%2C%20AccountLevel%20%2B%201%2C%20This.sNotes%2C%20This.iParentAcct%0A%20%20%20%20FROM%0A%20%20%20%20%20SF_REPTACCTS%20AS%20This%0A%20%20%20%20%20INNER%20JOIN%20CTE_DownLine%20AS%20Prior%20ON%20Prior.ID%20%3D%20This.iParentAcct%0A%20%20%20)%0A%20)%0A%20INSERT%20INTO%20%40tblReptAccts%20(ID%2C%20sCode%2C%20sSortCode%2C%20sRAName%2C%20dAmount%2C%20sNotes%2C%20iParentAcct)%0A%20%20SELECT%0A%20%20%20%20ID%2C%20%20sCode%2C%20sSortCode%2C%20sRAName%2C%20dAmount%2C%20sNotes%2C%20iParentAcct%0A%20%20FROM%0A%20%20%20%20CTE_DownLine%0A%20RETURN%0AEND%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EWhen%20I%20run%20the%20command%26nbsp%3BSELECT%20*%20FROM%20sf_ReptAcctsList('A')%20ORDER%20BY%20sSortCode%20then%20I%20get%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3EID%20%20sCode%20sSortCode%20sRAName%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dAmount%20sNotes%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20iParentAcct%0A%205%20%204%20%20%20%20%20%2004%20%20%20%20%20%20%20Revenue%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20125.61%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201%0A%206%20%204.1%20%20%20%2004.01%20%20%20%20%7C--%20Membership%20Dues%2FFees%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2030.29%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%205%0A%207%20%204.2%20%20%20%2004.02%20%20%20%20%7C--%20SSA%20Membership%20Fees%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2063.46%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%205%0A%208%20%204.3%20%20%20%2004.03%20%20%20%20%7C--%20Tow%20Fees%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20155.78%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%205%0A%209%20%204.4%20%20%20%2004.04%20%20%20%20%7C--%20Glider%20Rental%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2092.99%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%205%0A10%20%204.5%20%20%20%2004.05%20%20%20%20%7C--%20Miscellaneous%20Revenue%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20166.45%20%20Assessments%2C%20Donations%205%0A%202%20%205%20%20%20%20%20%2005%20%20%20%20%20%20%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2061.25%20%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201%0A%203%20%205.1%20%20%20%2005.01%20%20%20%20%7C--%20Facilities%20-%20Fixed%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20189.07%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202%0A29%20%205.1.1%20%2005.01.01%20%2B--%20---%20Airfield%20Lease%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2030.43%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%203%0A30%20%205.1.2%20%2005.01.02%20%2B--%20---%20Airfield%20Liability%20Insurance%20%20%20%20%20%20%20%20%20%20%20173.20%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%203%0A31%20%205.1.3%20%2005.01.03%20%2B--%20---%20Airfield%20Maintenance%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20163.06%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%203%0A32%20%205.1.4%20%2005.01.04%20%2B--%20---%20SSA%20Membership%20Fees%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20191.83%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%203%0A33%20%205.1.5%20%2005.01.05%20%2B--%20---%20Utilities%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2025.27%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%203%0A34%20%205.1.6%20%2005.01.06%20%2B--%20---%20Other%20Fixed%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2084.80%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%203%0A%204%20%205.2%20%20%20%2005.02%20%20%20%20%7C--%20Aircraft%20-%20Fixed%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2025.48%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202%0A35%20%205.2.1%20%2005.02.01%20%2B--%20---%20Aircraft%20Insurance%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20124.14%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%204%0A36%20%205.2.2%20%2005.02.02%20%2B--%20---%20Hangaring%2FTiedown%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20176.90%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%204%0A37%20%205.2.3%20%2005.02.03%20%2B--%20---%20Annual%20Inspections%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2090.75%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%204%0A38%20%205.2.4%20%2005.02.04%20%2B--%20---%20Aircraft%20Loan%20Interest%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20131.71%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%204%0A39%20%205.2.5%20%2005.02.05%20%2B--%20---%20Aircraft%20Licenses%20%26amp%3Bamp%3B%20Registrations%20%20161.55%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%204%0A40%20%205.3%20%20%20%2005.03%20%20%20%20%7C--%20Variable%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20143.31%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202%0A41%20%205.3.1%20%2005.03.01%20%2B--%20---%20Fuel%20and%20Oil%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20153.31%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2040%0A42%20%205.3.2%20%2005.03.02%20%2B--%20---%20Repair%20%26amp%3Bamp%3B%20Maintenance%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%207.30%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2040%0A43%20%205.4%20%20%20%2005.04%20%20%20%20%7C--%20Bad%20Debts%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2059.00%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%202%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ENow%20I%20wish%20to%20add%20total%20and%20subtotal%20rows%20presumably%20by%20using%20GROUP%20BY%20and%20ROLLUP%2C%20but%20I%20have%20not%20been%20able%20to%20figure%20out%20how%2C%20to%20achieve%20this%20output%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3EsCode%20sRAName%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20dAmount%20sNotes%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A4%20%20%20%20%20Revenue%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20125.61%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A4.1%20%20%20%7C--%20Membership%20Dues%2FFees%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2030.29%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A4.2%20%20%20%7C--%20SSA%20Membership%20Fees%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2063.46%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A4.3%20%20%20%7C--%20Tow%20Fees%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20155.78%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A4.4%20%20%20%7C--%20Glider%20Rental%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2092.99%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A4.5%20%20%20%7C--%20Miscellaneous%20Revenue%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20166.45%20%20Assessments%2C%20Donations%0A%20%20%20%20%20%20Total%20Revenue%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20634.58%0A5%20%20%20%20%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2061.25%20%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.1%20%20%20%7C--%20Facilities%20-%20Fixed%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20189.07%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.1.1%20%2B--%20---%20Airfield%20Lease%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2030.43%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.1.2%20%2B--%20---%20Airfield%20Liability%20Insurance%20%20%20%20%20%20%20%20%20%20%20173.20%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.1.3%20%2B--%20---%20Airfield%20Maintenance%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20163.06%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.1.4%20%2B--%20---%20SSA%20Membership%20Fees%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20191.83%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.1.5%20%2B--%20---%20Utilities%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2025.27%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.1.6%20%2B--%20---%20Other%20Fixed%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2084.80%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%7C--%20Total%20Facilities%20-%20Fixed%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20857.66%0A5.2%20%20%20%7C--%20Aircraft%20-%20Fixed%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2025.48%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.2.1%20%2B--%20---%20Aircraft%20Insurance%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20124.14%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.2.2%20%2B--%20---%20Hangaring%2FTiedown%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20176.90%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.2.3%20%2B--%20---%20Annual%20Inspections%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2090.75%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.2.4%20%2B--%20---%20Aircraft%20Loan%20Interest%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20131.71%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.2.5%20%2B--%20---%20Aircraft%20Licenses%20%26amp%3Bamp%3B%20Registrations%20%20161.55%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%7C--%20Total%20Aircraft%20-%20Fixed%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20710.53%0A5.3%20%20%20%7C--%20Variable%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20143.31%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.3.1%20%2B--%20---%20Fuel%20and%20Oil%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20153.31%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A5.3.2%20%2B--%20---%20Repair%20%26amp%3Bamp%3B%20Maintenance%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%207.30%20%20NULL%0A%20%20%20%20%20%20%7C--%20Total%20Variable%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20303.92%0A5.4%20%20%20%7C--%20Bad%20Debts%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2059.00%20%20NULL%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%7C--%20Total%20Bad%20Debts%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2059.00%0A%20%20%20Total%20Expenses%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%201931.11%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20am%20not%20sure%20whether%20the%20last%20step%20should%20happen%20inside%20the%20above%20function%2C%20or%20in%20a%20separate%20stored%20procedure.%20Your%20help%20in%20writing%20the%20appropriate%20SQL%20code%20is%20greatly%20appreciated!%20This%20sample%20shows%20three%20levels%20of%20accounts%20and%20subaccounts%3B%20it%20would%20be%20desirable%20if%20the%20code%20could%20handle%20any%20number%20of%20levels%20in%20the%20account%20hierarchy.%3C%2FP%3E%3CP%3EThanks%20so%20much!%3C%2FP%3E%3CP%3Er4%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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