Click here to monitor SSC
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.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1132 Visits: 282
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-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44768 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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

Group: General Forum Members
Points: 1132 Visits: 282
Thanks Jeff, I will review the article, it looks exactly what I need.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44768 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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