Best Way to separate this... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Best Way to separate this...

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-05-31 : 10:16:40

Dear All,

Thanks for help as usual.

Kindly see below:

DECLARE @trans_tbl table (tran_id int, tran_date int, credit_amt money, debit_amt money )

insert into @trans_tbl (tran_id, tran_date, credit_amt, debit_amt)
select 1, 2011-04-01, -2.00, 0.00 UNION ALL
select 2, 2011-04-02, 0.00, 500.00 UNION ALL
select 3, 2011-04-09, -54.00, 900.00 UNION ALL
select 4, 2011-04-19, -20.00, 0.00 UNION ALL
select 5, 2011-04-22, 0.00, 0.00 UNION ALL
select 6, 2011-04-30, 0.00, 343.00 UNION ALL

My Expected Result

tran_id tran_date Amount Type
1 2011-04-01 -2.00 Liability
2 2011-04-02 500.00 Asset
3 2011-04-09 -54.00 Liability
3 2011-04-09 900.00 Asset
4 2011-04-19 -20.00 Liability
5 2011-04-22 0.00 Asset
6 2011-04-30 343.00 Asset

Kindly note,
- tran_id 3 return both figures
- tran_id 5 returns 0.00 (If possible the record can be exempted)

Many thanks.

I sign for fame not for shame but all the same, I sign my name.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-31 : 10:26:43

select tran_id, tran_date, credit_amt as Amount
,CASE WHEN credit_amt > 0 Then 'Asset' Else 'Liability' End as [Type]
from @trans_tbl
where credit_amt <> 0
union
select tran_id, tran_date, debit_amt as Amount
,CASE WHEN debit_amt > 0 Then 'Asset' Else 'Liability' End as [Type]
from @trans_tbl
where debit_amt <> 0

Jim

Everyday I learn something that somebody else already knew

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-06-01 : 04:25:05

Thanks Boss Jim... Very grateful...

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources