CTE and totals for an Accouting plan

Question

Post reply

CTE and totals for an Accouting plan

Mauricio_

SSCrazy

Points: 2935
More actions
July 19, 2013 at 7:00 am

#277623

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_IDAP_CODENAME
110000Assets 650
711000 Current Assets 650
811100 Cash 100
1211200 Bank 500
2511300 Tax payed 50
612000 Fixed Assets
1312100 Office Equipment
1412200 Vehicle
220000Liabilities
1521000 Current Liabilities
1721100 Credit Card
1821200 Accounts Payable
2421300 Tax to pay
1622000 Long Term Liabilities
1922100 Bank Loan
330000Equity
440000Expenses
2041000 Cost of Goods Sold
550000Incomes 650
2151000 Sales 650
2251100 Sales of Products
2351200 Services

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mauricio_ SSCrazy Points: 2935 More actions · Answer 2

Thanks Jeff, I will review the article, it looks exactly what I need.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)