Posted by Scripty on 12/18/06 16:56

Just a quick note:

this nested "case when" works in MSSQL Server. It it better than the
temporary table approach not only as it is faster, but also because it
picks up dates where zero debits occur, as well as days when they do.
My original temporary table approach only picks out dates that match
from both initial queries.
I must read up on using 'case when....' in statements.

Thanks again

S


Scripty wrote:
> Thanks Guys,
>
> I will give this a go. Is this T-SQL / pl-SQL or is this a technique
> used only on SQL Server ?
>
> Yes, Ed, there's primary keys, indices, etc etc on this table. Just
> wanted to post the bare bones of the problem, rather than labour you
> with all the other details..
>
> cheers,
>
> Scripty.
>
>
> Ed Murphy wrote:
> > markc600@hotmail.com wrote:
> >
> > > select date,
> > > user,
> > > sum(case when transaction_type ='debit' then amount else 0 end)
> > > /
> > > sum(amount) as perc_Debit
> > > from mytable
> > > group by date, user
> >
> > You're right, I guess coalesce() isn't needed. (It would have been
> > needed in the temp-tables approach, if you wanted to pick up date/user
> > combinations with 0% debits.)

[Back to original message]