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_Database
go
; 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
), AccountPart1
AS
(
Select
Level=1,
AccountID,
Display=convert(varchar(1000),AccountName),
ParentID,
AccountOrder
From BalanceSheet A
Where A.ParentID is NULL
Union All
Select
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 AccountPart1
Order By COALESCE(AccountOrder,ParentID)
And here is the result. I marked red those things that needs to be achieved.
Level AccountID Display ParentID AccountOrder
1 1 ASSETS NULL 1
3 2 Current Assets 1 2
5 3 Checking/Savings 2 3
7 4 Bank 3 4
9 275 Cash on Drawer_2 4 NULL
9 276 Cash in Bankr_2 4 NULL
Total Bank
Total Checking/Savings
5 5 Accounts Receivable 2 5
7 283 Owners Equity 5 NULL
7 284 Sales 5 NULL
7 285 Shipping & Delivery Income 5 NULL
7 286 Freight Cost 5 NULL
Total Accounts Receivable
5 6 Other Current Assets 2 6
Total Current Assets
3 7 Fixed Asset 1 7
3 8 Other Asset 1 8
Total ASSETS
1 9 LIABILITIES & EQUITY NULL 9
3 10 Liabilities 9 10
5 11 Current Liabilities 10 11
7 12 Accounts Payable 11 12
9 289 Bank Charges 12 NULL
9 290 Computer Expenses 12 NULL
9 291 Depreciation Expense 12 NULL
9 292 Insurance Enxpense 12 NULL
9 293 Interest Expense 12 NULL
9 294 Meals & Intertainment 12 NULL
Total Accounts Payable
7 13 Credit Card 11 13
9 300 Repair & Maintainance 13 NULL
9 301 Taxes - Property 13 NULL
9 302 Communication 13 NULL
9 303 Transportation 13 NULL
9 304 Tax Payable 13 NULL
Total Credit Card
7 14 Other Current Liability 11 14
Total Current Liabilities
5 15 Long Term Liability 10 15
Total Liabilities
3 16 Equity 9 16
5 17 Net Income 16 17
Total Equity
Total LIABILITIES & EQUITY
Thank you!
-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Sub Total of Accounts or Sub Accounts?
Posted - 11/06/2012 : 07:08:22
