How to separate positive and negative numbers into their own columns?
I'm not sure about the exact syntax in Sybase, but you should be able to group on the date and sum up the positive and negative values:
select activity_dt, sum(case when activity_amt < 0 then activity_amt else 0 end) as debits, sum(case when activity_amt >= 0 then activity_amt else 0 end) as creditsfrom theTablegroup by activity_dt
I found a new answer to this problem using the DECODE
function. I hope this turns out to be useful for everyone.
select activity_dt, sum((DECODE(activity_amt /-ABS(activity_amt), 1, activity_amt, 0))) as credits,sum((DECODE(activity_amt /-ABS(activity_amt), -1, activity_amt, 0))) as debitsfrom the_tablegroup by activity_dtorder by activity_dt;