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.
Author |
Topic |
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2013-01-28 : 19:12:37
|
Standard Case questionI 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 BalamtThen 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 CreditCase 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 DebitFROM [ar-open-tr] --Chandu |
|
|
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 claritySELECT 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 DebitFROM [ar-open-tr] T1What about scenarios where [ar-open-tr].[Trans-amt]=0?SQL Server Professional http://sqlbay.blogspot.in |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-01-29 : 08:16:07
|
>= 0, would go as CreditSELECT 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 DebitFROM [ar-open-tr] T1 |
|
|
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2013-01-29 : 09:25:21
|
perfect thank you! |
|
|
|
|
|