SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case When very simple im sure.		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Yak Posting Veteran

66 Posts
Posted - 01/28/2013 :  19:12:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1717 Posts
Posted - 01/28/2013 :  23:33:07  Show Profile  Reply with Quote
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 - 01/29/2013 :  00:51:47  Show Profile  Reply with Quote
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

152 Posts
Posted - 01/29/2013 :  08:16:07  Show Profile  Reply with Quote
>= 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
Yak Posting Veteran

66 Posts
Posted - 01/29/2013 :  09:25:21  Show Profile  Reply with Quote
perfect thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000