Re: Quick, simple crosstab question

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 12:40:00 -0500

Thanks for posting DDL and sample data! Makes questions very easy to
answer...

This will do what you need:

select office_num,
   sum(case when CurrentBalance > 0 then CurrentBalance else 0 end) as
TotalDebitBalances,
   sum(case when CurrentBalance < 0 then CurrentBalance else 0 end) as
TotalCreditBalances 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Carl Imthurn" <nospam@all.com> wrote in message
news:%23JEvD2NyEHA.924@TK2MSFTNGP10.phx.gbl...
> Hi folks --
>
> Probably simple, but I just can't seem to get there.
>
> My achieved output (so far):
>
> Off#    DbBals          CrBals
> 1       210.0000        NULL
> 2       60.0000         NULL
> 3       100.0000        NULL
>
> My desired output:
>
> Off#    DbBals          CrBals
> 1       330.0000        -120.0000
> 2       60.0000         NULL
> 3       100.0000        NULL
>
> In other words, sum the debit balances into the 2nd column and the credit
balances into
> the 3rd column.
>
>
> Here's some DDL:
>
> CREATE TABLE #temp (
>    OFFICE_NUM int NOT NULL,
>    PatientID int NOT NULL,
>    PatientType int NOT NULL ,
>    CurrentBalance money NOT NULL
> ) ON [PRIMARY]
>
> INSERT INTO #temp VALUES(1,1,2,40)
> INSERT INTO #temp VALUES(1,2,2,-100)
> INSERT INTO #temp VALUES(1,3,2,50)
> INSERT INTO #temp VALUES(1,4,2,-20)
> INSERT INTO #temp VALUES(1,5,2,80)
> INSERT INTO #temp VALUES(1,6,2,80)
> INSERT INTO #temp VALUES(1,7,2,80)
> INSERT INTO #temp VALUES(2,93,2,30)
> INSERT INTO #temp VALUES(2,94,2,30)
> INSERT INTO #temp VALUES(3,2,2,100)
>
> --select * from #temp
> -- here's what I've tried so far:
>
> select office_num,
>    case when sum(CurrentBalance) > 0 then sum(CurrentBalance) else null
end as
> TotalDebitBalances,
>    case when sum(CurrentBalance) < 0 then sum(CurrentBalance) else null
end as
> TotalCreditBalances
>
> from #temp
> group by office_num
> order by office_num
>
> drop table #temp
>
> Any and all help is appreciated -- thanks in advance.
>
> Carl
>
Loading