On 25/07/2009 11:59 AM, David Bicking wrote:
> On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote:
>> An accounting system where the sign of the amount is detached and has to
>> be obtained from another column is tedious and error-prone; obtaining it
>> from TWO columns is "interesting";
> Y
On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote:
> On 25/07/2009 6:17 AM, David Bicking wrote:
> > That works. Thanks!
>
> It struck me that Pavel's revised query didn't mention the d2 column at
> all, only d1:
>
Thanks for the additional info. In real life, I added WHERE d1='X' OR
D2='X'
On 25/07/2009 6:17 AM, David Bicking wrote:
> That works. Thanks!
It struck me that Pavel's revised query didn't mention the d2 column at
all, only d1:
>> sum(case when d1='X' then 1 else -1 end) as act_sum,
>> sum(case when d1='X' then amt else -amt end) as net
... backtracking, it seems that
That works. Thanks!
> From: Pavel Ivanov
> Oops, sorry! I misunderstood what you
> need. Try this:
>
> select id,
> case when act_sum = 1 then 'NEW'
> when act_sum = 0 then 'CHANGE'
> else 'DROP'
> end as Action,
> net
> from
> (
> select id,
> sum(case when d1='X' then 1 else -1 end) as act_su
Oops, sorry! I misunderstood what you need. Try this:
select id,
case when act_sum = 1 then 'NEW'
when act_sum = 0 then 'CHANGE'
else 'DROP'
end as Action,
net
from
(
select id,
sum(case when d1='X' then 1 else -1 end) as act_sum,
sum(case when d1='X' then amt else -amt end) as net
from Source
gro
> From: Pavel Ivanov
>
> Just do the same approach with CASE you've already used:
>
> Select ID,
> CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW'
> WHEN d1 IS NULL AND d2='X' THEN 'DROP'
> ELSE 'CHANGE' END AS Action,
> Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN
> d2='X' THE
> And how can I calculate the value for Action?
Just do the same approach with CASE you've already used:
Select ID,
CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW'
WHEN d1 IS NULL AND d2='X' THEN 'DROP'
ELSE 'CHANGE' END AS Action,
Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN d2='
I am trying to create a report. The source table can have one or two records
for each given ID. If there is a record with D1='X' but no record with D2='X',
then it is a "NEW" action. If there are both D1 and D2 record, it is a "CHANGE"
and the Net amount is the D1 amount less the D2 amount. If t
8 matches
Mail list logo
