Anybody can help me just one more step to have a working balance sheet. I have this query which generates the balance sheet base on its format. But I need to have a sub total or total of each account which has sub accounts..The following query works fine but it does not display the sub total. The proposed result below must be achieve. Total marked in red. Here is my query:use POS_Databasego; with BalanceSheet (AccountID, AccountName, ParentID,AccountOrder)As ( Select AccountID, Display=convert(varchar(1000),AccountName), ParentID, AccountOrder From BalanceSheet_Template UNION ALL Select (-1) * AccountID, Display=convert(varchar(1000),AccountName), subaccntid, NULL From tbl_accountlist ), AccountPart1AS(Select Level=1, AccountID, Display=convert(varchar(1000),AccountName), ParentID, AccountOrderFrom BalanceSheet AWhere A.ParentID is NULLUnion AllSelect Level=B.Level + 2, ABS(A.AccountID), Display=convert(varchar(1000),replicate(' ', B.Level) + AccountName), A.ParentID, A.AccountOrder From BalanceSheet A Inner Join AccountPart1 B On B.AccountID = A.ParentID)Select * From AccountPart1Order By COALESCE(AccountOrder,ParentID)
And here is the result. I marked red those things that needs to be achieved.Level AccountID Display ParentID AccountOrder1 1 ASSETS NULL 13 2 Current Assets 1 25 3 Checking/Savings 2 37 4 Bank 3 49 275 Cash on Drawer_2 4 NULL9 276 Cash in Bankr_2 4 NULL Total Bank Total Checking/Savings5 5 Accounts Receivable 2 57 283 Owners Equity 5 NULL7 284 Sales 5 NULL7 285 Shipping & Delivery Income 5 NULL7 286 Freight Cost 5 NULL Total Accounts Receivable5 6 Other Current Assets 2 6 Total Current Assets3 7 Fixed Asset 1 73 8 Other Asset 1 8 Total ASSETS1 9 LIABILITIES & EQUITY NULL 93 10 Liabilities 9 105 11 Current Liabilities 10 117 12 Accounts Payable 11 129 289 Bank Charges 12 NULL9 290 Computer Expenses 12 NULL9 291 Depreciation Expense 12 NULL9 292 Insurance Enxpense 12 NULL9 293 Interest Expense 12 NULL9 294 Meals & Intertainment 12 NULL Total Accounts Payable7 13 Credit Card 11 139 300 Repair & Maintainance 13 NULL9 301 Taxes - Property 13 NULL9 302 Communication 13 NULL9 303 Transportation 13 NULL9 304 Tax Payable 13 NULL Total Credit Card7 14 Other Current Liability 11 14 Total Current Liabilities5 15 Long Term Liability 10 15 Total Liabilities3 16 Equity 9 165 17 Net Income 16 17 Total Equity Total LIABILITIES & EQUITY
Thank you!-------------------------------------------------------------------------------------------------------Learning MS SQL Server 2008