SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE and totals for an Accouting plan


CTE and totals for an Accouting plan

Author
Message
Mauricio_
Mauricio_
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 291
Hi guys,
I have a simple database with 2 tables. Here are the structures:

CREATE TABLE [dbo].[Accounts](
[ACC_ID] [int] IDENTITY(1,1) NOT NULL,
[ACC_CODE] [char](5) NOT NULL,
[ACC_NAME] [nvarchar](50) NOT NULL,
[ACC_PARENT_ID] [int] NULL,
[ACC_USABLE] [tinyint] NOT NULL,
CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED
(
[ACC_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [Accounts_uq] UNIQUE NONCLUSTERED
(
[ACC_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


ALTER TABLE [dbo].[Accounts] WITH CHECK ADD CONSTRAINT [FK_Accounts_Accounts] FOREIGN KEY([ACC_PARENT_ID])
REFERENCES [dbo].[Accounts] ([ACC_ID])
GO

ALTER TABLE [dbo].[Accounts] CHECK CONSTRAINT [FK_Accounts_Accounts]
GO


CREATE TABLE [dbo].[Ledgers](
[LED_ID] [bigint] IDENTITY(1,1) NOT NULL,
[LED_NUMBER] [int] NOT NULL,
[LED_DATE] [datetime] NOT NULL,
[LED_ACC_ID] [int] NOT NULL,
[LED_AMOUNT] [decimal](18, 3) NOT NULL,
[LED_COLUMN] [char](1) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Ledgers] ADD CONSTRAINT [DF_Ledgers_LED_COLUMN] DEFAULT ('D') FOR [LED_COLUMN]
GO

ALTER TABLE [dbo].[Ledgers] WITH CHECK ADD CONSTRAINT [FK_Ledgers_Accounts] FOREIGN KEY([LED_ACC_ID])
REFERENCES [dbo].[Accounts] ([ACC_ID])
GO

ALTER TABLE [dbo].[Ledgers] CHECK CONSTRAINT [FK_Ledgers_Accounts]
GO

ALTER TABLE [dbo].[Ledgers] WITH CHECK ADD CONSTRAINT [CK_LED_COLUMN] CHECK (([LED_COLUMN]='C' OR [LED_COLUMN]='D'))
GO

ALTER TABLE [dbo].[Ledgers] CHECK CONSTRAINT [CK_LED_COLUMN]
GO

With this CTE I show the accounting plan:

WITH AccountingPlan (AP_Parent, AP_ID, AP_CODE, AP_NAME, AP_LEVEL)
AS
(
SELECT A.ACC_PARENT_ID, A.ACC_ID, A.ACC_CODE, A.ACC_NAME, 0 AS AP_LEVEL
FROM Accounts A
WHERE A.ACC_PARENT_ID IS NULL
UNION ALL
SELECT A.ACC_PARENT_ID, A.ACC_ID, A.ACC_CODE, A.ACC_NAME, AP_LEVEL + 1
FROM Accounts A
INNER JOIN PlanDeCuentas AS AP
ON A.ACC_PARENT_ID = AP.AP_ID
)
SELECT AP_ID, AP_CODE, replicate(' ', AP_LEVEL * 10) + AP_NAME AS NAME FROM AccountingPlan
ORDER BY AP_CODE

So, my question is how to show totals for each level, something like this:totales? Es decir, quiero algo así:

AP_ID AP_CODE NAME
1 10000 Assets 650
7 11000 Current Assets 650
8 11100 Cash 100
12 11200 Bank 500
25 11300 Tax payed 50
6 12000 Fixed Assets
13 12100 Office Equipment
14 12200 Vehicle
2 20000 Liabilities
15 21000 Current Liabilities
17 21100 Credit Card
18 21200 Accounts Payable
24 21300 Tax to pay
16 22000 Long Term Liabilities
19 22100 Bank Loan
3 30000 Equity
4 40000 Expenses
20 41000 Cost of Goods Sold
5 50000 Incomes 650
21 51000 Sales 650
22 51100 Sales of Products
23 51200 Services
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87486 Visits: 41116
If I'm looking at your problem correctly, the following article should help...
http://www.sqlservercentral.com/articles/T-SQL/94570/

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mauricio_
Mauricio_
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 291
Thanks Jeff, I will review the article, it looks exactly what I need.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87486 Visits: 41116
Mauricio N (7/19/2013)
Thanks Jeff, I will review the article, it looks exactly what I need.


How'd things turn out?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search