Hi SQL Gurus,
I would like to run a query to display the amount in a debit and credit column from a FACT table. The value appearing in the debit or credit column depends on the account sign in the ACCOUNT table.
FACT TABLE
ACCOUNT | ORGANIZATION | YEAR | PERIOD | AMOUNT
-----------------------------------------------------
1001 | aaa | 2012 | 01 | 100
1002 | aaa | 2012 | 01 | 50
1003 | aaa | 2012 | 01 | -100
1004 | aaa | 2012 | 01 | 90
ACCOUNT TABLE
ACCOUNT | DESC | SIGN
------------------------
1001 | 1001 | Dr
1002 | 1002 | Cr
1003 | 1003 | Dr
1004 | 1004 | Cr
How can I transform to this result?
ACCOUNT | ORGANIZATION | YEAR | PERIOD | Dr | Cr
-----------------------------------------------------
1001 | aaa | 2012 | 01 | 100 |
1002 | aaa | 2012 | 01 | | 50
1003 | aaa | 2012 | 01 | -100 |
1004 | aaa | 2012 | 01 | | 90
Below is the DDL and sample data for this:
DECLARE @account TABLE
(
ACCOUNT CHAR(4) NOT NULL,
DESC CHAR(4) NOT NULL,
SIGN CHAR(4) NOT NULL
)
DECLARE @FACT TABLE
(
ACCOUNT CHAR(4) NOT NULL,
ORGANIZATION CHAR(3) NOT NULL,
YEAR SMALLINT NOT NULL,
PERIOD CHAR(2) NOT NULL,
AMOUNT int NOT NULL
)
INSERT @account
VALUES ('1001', '1001', 'Dr'),
('1002', '1002', 'Cr'),
('1003', '1003', 'Dr'),
('1004', '1004', 'Cr')
INSERT @FACT
VALUES ('1001', 'aaa', 2012, '01', 100),
('1002', 'aaa', 2012, '01', 50),
('1003', 'aaa', 2012, '01', -100),
('1004', 'aaa', 2012, '01', 90)
Thanks.