July 19, 2013 at 7:00 am
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
July 19, 2013 at 8:03 am
If I'm looking at your problem correctly, the following article should help...
http://qa.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
July 19, 2013 at 8:36 am
Thanks Jeff, I will review the article, it looks exactly what I need.
July 25, 2013 at 8:06 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply