Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sub Total of Accounts or Sub Accounts?

Author  Topic 

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-11-06 : 07:08:22
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 12:17:11
Is this for reporting tool? then there's no need of doing this in sql. Most reporting tools have subtotal available as an option inside grouping properties

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-11-06 : 19:46:01
Thank you visakh16!

Yes this is part of the report that will generate the balance sheet base on the transactions table. We do not have any reporting tools at hand. I do not have options to do but to make this running by just in sql server. I have seen some examples that uses grouping_id, rollup and cube and I hope someone can help me incorporate those functions to my query

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
   

- Advertisement -