Spliting A field into Positive and Negative Values

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 2000 Forums

Transact-SQL (2000)

Spliting A field into Positive and Negative Values

Author

Topic

bbrendan
Starting Member

3 Posts

Posted - 2004-08-26 : 04:46:35

Hi All,

Im just after some assistance in trying to take a field [net-val] and split it into two fields.

The field in question contains both Postive and negative values like
[Net-Val]
100.50
25.25
14.55
-10.55
-79.98

What I would like to do is break thi out into two fields like

Debit Val Credit Val
-----------+-------------
100.50
25.25
14.55
-10.55
-79.98

-----------+-------------

This is my sql so far. Is basically joins two tables and groups up by [Rep Code].

I would like to add in two fields to Split out the [Net-Val] field into debit Val and Credit Val

SELECT h.[rep Code], e.Exec_CodeDescription,e.Exec_Link,sum(h.[net-val]) as Value, sum(h.Qty) as Qty
FROM Saptsd_hist h
Inner join tblExecCodes e on h.[rep code] = e.Exec_code
where h.File_Date between '2004-08-01 00:00:00.000' and '2004-08-31 23:59:59.000'
group by h.[Rep Code],e.Exec_CodeDescription, e.Exec_Link
order by e.[Exec_link]

thankyou
brendan

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 05:12:32

and how would you like to split them? based on what. there is nothing in [Credit val] column in your example...

Go with the flow & have fun! Else fight the flow :)

bbrendan
Starting Member

3 Posts

Posted - 2004-08-26 : 05:43:38

Ah,

Sorry Looks like it didnt format correctly

Debit Val Credit Val
-----------+-------------
100.50
25.25
14.55
.............-10.55
.............-79.98

-----------+-------------

ps please ignor the "." dots its the only way I could get it to work!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-26 : 05:52:10

will this do?

select case when [Net-Val]>0 then col1 else null end as [Debit Val],
case when [Net-Val]<0 then col1 else null end as [Credit Val]
from MyTable

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources