Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CTE and totals for an Accouting plan Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 7:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:11 PM
Points: 399, Visits: 156
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
Post #1475448
Posted Friday, July 19, 2013 8:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 35,347, Visits: 31,882
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475501
Posted Friday, July 19, 2013 8:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:11 PM
Points: 399, Visits: 156
Thanks Jeff, I will review the article, it looks exactly what I need.
Post #1475520
Posted Thursday, July 25, 2013 8:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 35,347, Visits: 31,882
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1477831
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse