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 2008 Forums
 Transact-SQL (2008)
 Case When very simple im sure.

Author  Topic 

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-01-28 : 19:12:37
Standard Case question

I have a field called [ar-open-tr].[Trans-amt] that has the balance debits and credits. I am sure you are seeing where I am going with this.

I would like to have 3 columns first column is [ar-open-tr].[Trans-amt] As Balamt

Then I would like case when's setup.

Aka Case when [ar-open-tr].[Trans-amt] AS Balamt >0 then (new Column)[ar-open-tr].[Trans-amt] AS Credit

Case when [ar-open-tr].[Trans-amt] AS Balamt <0 THEN (New Column)
[ar-open-tr].[Trans-amt] AS Debit

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-28 : 23:33:07
May be this?

SELECT [ar-open-tr].[Trans-amt],
Case when [ar-open-tr].[Trans-amt] > 0 then [ar-open-tr].[Trans-amt] END AS Credit,
Case when [ar-open-tr].[Trans-amt] < 0 then [ar-open-tr].[Trans-amt] END AS Debit
FROM [ar-open-tr]





--
Chandu
Go to Top of Page

sqlbay
Starting Member

12 Posts

Posted - 2013-01-29 : 00:51:47
You can include a table alias instead of [ar-open-tr] for more clarity
SELECT T1.[Trans-amt],
Case when T1.[Trans-amt] > 0 then T1.[Trans-amt] END AS Credit,
Case when T1.[Trans-amt] < 0 then T1.[Trans-amt] END AS Debit
FROM [ar-open-tr] T1

What about scenarios where [ar-open-tr].[Trans-amt]=0?

SQL Server Professional http://sqlbay.blogspot.in
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-01-29 : 08:16:07
>= 0, would go as Credit

SELECT T1.[Trans-amt],
Case when T1.[Trans-amt] >= 0 then T1.[Trans-amt] END AS Credit,
Case when T1.[Trans-amt] < 0 then T1.[Trans-amt] END AS Debit
FROM [ar-open-tr] T1
Go to Top of Page

hbadministrator
Posting Yak Master

120 Posts

Posted - 2013-01-29 : 09:25:21
perfect thank you!
Go to Top of Page
   

- Advertisement -