January 7, 2012 at 10:59 am
Hi,
I have two tables:
Table 1
Account Amount
1 1000
2 500
3 1000
Table 2
CostCenter Account Amount
a 1 100
b 1 200
c 1 300
a 2 200
d 2 200
What I want to have is:
Account CostCenter Amount
1 a 100
1 b 200
1 c 300
1 9999 400 (Rest: 1000-100-200-300)
2 a 200
2 d 200
2 9999 100 (Rest: 500-200-200)
3 9999 1000 (Rest: 1000-0)
Your help is highly appreciated.
Thanks, Chris
January 7, 2012 at 11:58 am
DECLARE @Table1 TABLE (Account INT, Amount DECIMAL(10, 0))
DECLARE @Table2 TABLE (CostCenter VARCHAR(10), Account INT, Amount DECIMAL(10, 0))
INSERT INTO @Table1 VALUES
(1, 1000), (2, 500), (3, 1000)
INSERT INTO @Table2 VALUES
('a', 1, 100), ('b', 1, 200), ('c', 1, 300),
('a', 2, 200), ('d', 2, 200)
;WITH Cte AS
(
SELECT
Account, CostCenter, CostCenter AS [Order], Amount
FROM
@Table2 T1
UNION ALL
SELECT
T1.Account, '9999', CHAR(255), T1.Amount - COALESCE(SUM(T2.Amount), 0)
FROM
@Table1 T1
LEFT JOIN
@Table2 T2 ON T2.Account = T1.Account
GROUP BY
T1.Account, T1.Amount
)
SELECT
Account, CostCenter, Amount
FROM
Cte
ORDER BY
Account, [Order]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply