SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sub Total of Accounts or Sub Accounts?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 11/06/2012 :  07:08:22  Show Profile  Reply with Quote
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

Edited by - adbasanta on 11/06/2012 07:10:31

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/06/2012 :  12:17:11  Show Profile  Reply with Quote
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 - 11/06/2012 :  19:46:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000